CODEWITHSUNDEEP
sqloracle-database
nodev_101
nodev_101

Reputation: 109

Need to separate last_name and domain from mail id

I have sample mail-id like below:

[email protected]
[email protected]
[email protected]

So, I need the output like below:

das   Gmail
Khan  gmail
bera  yahoo

Using regexp am able to do it, but It's not giving correct output when mail id is coming with middle name. Can anyone please help me on it.

Upvotes: 0

Views: 57

Answers (1)

MT0
MT0

Reputation: 168740

You can use the simple string functions INSTR and SUBSTR (which are much faster than regular expressions):

SELECT SUBSTR(local_part, INSTR(local_part, '.', -1) + 1) AS last_name,
       SUBSTR(domain, 1, INSTR(domain, '.') - 1) AS domain
FROM   (
  SELECT SUBSTR(email, 1, INSTR(email, '@', -1) - 1) AS local_part,
         SUBSTR(email, INSTR(email, '@', -1) + 1) AS domain
  FROM   table_name
)

Which, for the sample data:

CREATE TABLE table_name(email) AS
SELECT '[email protected]' FROM DUAL UNION ALL
SELECT '[email protected]' FROM DUAL UNION ALL
SELECT '[email protected]' FROM DUAL;

Outputs:

LAST_NAME DOMAIN
das example
khan example
bera example

If you did want to use regular expression (which will be slower) then:

SELECT REGEXP_SUBSTR(email, '([^.]+)@([^.]+)', 1, 1, NULL, 1) AS last_name,
       REGEXP_SUBSTR(email, '([^.]+)@([^.]+)', 1, 1, NULL, 2) AS domain
FROM   table_name;

Gives the same output.

fiddle

Upvotes: 1

Related Questions