Need to separate last_name and domain from mail id

Question

I have sample mail-id like below:

raju.das@gmail.com
javed.khan@gmail.com
Amit.kumar.bera@yahoo.com

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.

Regexp it's slow you should consider something else to make something like that, check this https://stackoverflow.com/questions/26878291/split-string-by-delimiter-position-using-oracle-sql — Gicu Aftene, Dec 15 '22 at 12:58

MT0 · Answer 1 · 2022-12-15T13:48:39.527

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 'raju.das@example.com' FROM DUAL UNION ALL
SELECT 'javed.khan@example.com' FROM DUAL UNION ALL
SELECT 'Amit.kumar.bera@example.com' 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

Need to separate last_name and domain from mail id

1 Answers1