CODEWITHSUNDEEP
sqlregexoracle-database
Erik
Erik

Reputation: 396

Is there a simplest way to select only the domain name from email address in oracle?

I have a table called email_id and column name as email_id and I have these email id’s in the column 

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

And I need only domain names like these as result

yahoo
msn
gmail
xmsoftware

Is there a simplest way (or) any other function than this?

select substr(email_id.email_id,(instr(email_id.email_id,'@',1)+1),(instr(email_id.email_id,'.',1) - (instr(email_id.email_id,'@',1)+1))) as domain 
from email_id;

Upvotes: 3

Views: 4977

Answers (2)

Kaushik Nayak
Kaushik Nayak

Reputation: 31648

I would prefer REGEXP_SUBSTR

select REGEXP_SUBSTR(email_id,'^.*@(.*)\.(.*)$',1,1,null,1) as domain 
 FROM emails;--                     ^ first match        ^ within()

Demo

Upvotes: 5

Littlefoot
Littlefoot

Reputation: 142720

The oldfashioned way is to use SUBSTR + INSTR combination. It takes more letters to type (when compared to regular expressions), but might work faster on large data sets.

SQL> WITH email_id (email_id)
  2       AS (SELECT '[email protected]' FROM DUAL
  3           UNION ALL
  4           SELECT '[email protected]' FROM DUAL
  5           UNION ALL
  6           SELECT '[email protected]' FROM DUAL
  7           UNION ALL
  8           SELECT '[email protected] ' FROM DUAL)
  9  select email_id,
 10         substr(email_id,
 11                instr(email_id, '@') + 1,
 12                instr(email_id, '.', instr(email_id, '@')) - instr(email_id, '@') - 1) result
 13  from email_id;

EMAIL_ID             RESULT
-------------------- --------------------
[email protected]       yahoo
[email protected]         msn
[email protected]       gmail
[email protected]  xmsoftware

SQL>

Upvotes: 1

Related Questions