CODEWITHSUNDEEP
sqloracle-database
Jayanth Raghava
Jayanth Raghava

Reputation: 33

Extracting First_name & Last_name from email oracle using Reg_exp

How to extract First_name & last_name from email using oracle REGEXP_SUBSTR,

Email: [email protected]

Expected output:

First_name Last_name
susan ryan 
select
  substr('[email protected]',1,(INSTR('[email protected]','.')-1)) first_name,
  substr('[email protected]',(INSTR('[email protected]','.')+1),(INSTR('[email protected]','@'))) last_name
from dual;

But i'm getting result as

first_name Last_name
susan ryan@email.

Upvotes: 1

Views: 1398

Answers (2)

Thorsten Kettner
Thorsten Kettner

Reputation: 94874

You have

substr(email, instr(email, '.') + 1, instr(email, '@')) as last_name

But the second parameter is not the end position, but the requested length, so you must subtract the position of the dot:

substr(email, instr(email, '.') + 1, instr(email, '@') - instr(email, '.') - 1) as last_name

That's easier with REGEXP_SUBSTR by the way:

regexp_substr(email, '[[:alpha:]]+', 1, 1) as first_name,
regexp_substr(email, '[[:alpha:]]+', 1, 2) as last_name

We are looking for substrings that only consist of letters in the e-mail here. For first_name we are taking the first such string, for last_name the second one. This relies of course on all e-mails in your table equally consisting of firstname.lastname@domain.

Here is the docs on REGEXP_SUBSTR: https://docs.oracle.com/en/database/oracle/oracle-database/19/sqlrf/REGEXP_SUBSTR.html#GUID-2903904D-455F-4839-A8B2-1731EF4BD099

Upvotes: 1

Koen Lostrie
Koen Lostrie

Reputation: 18630

Here are some examples of how to do this. First remove the domain using SUBSTR (column "domain") or REGEXP (column "domain_regexp"), then split the portion before the domain (column "no_domain") using REGEXP_SUBSTR:

WITH samples AS
(
  SELECT '[email protected]' as str FROM DUAL UNION
  SELECT '[email protected]' as str FROM DUAL
)
SELECT 
str as email,
REGEXP_SUBSTR(str,'@.+$') AS domain_regexp,
SUBSTR(str, INSTR(str,'@')) as domain,
SUBSTR(str, 1, INSTR(str,'@') - 1) as no_domain,
REGEXP_SUBSTR(SUBSTR(str, 1, INSTR(str,'@') - 1),'[^.]+',1,1) AS first_name,
REGEXP_SUBSTR(SUBSTR(str, 1, INSTR(str,'@') - 1),'[^.]+',1,2) AS last_name
from samples;

EMAIL                 DOMAIN_REGEXP         DOMAIN                NO_DOMAIN             FIRST_NAME            LAST_NAME            
--------------------- --------------------- --------------------- --------------------- --------------------- ---------------------
[email protected] @email.com            @email.com            -susan.ryan           -susan                ryan                 
[email protected]       @email.com            @email.com            roger                 roger

Upvotes: 0

Related Questions