Question: I have a table called **email\_id** and column name as **email\_id** and I have these email id’s in the column 

```
name@yahoo.com
name@msn.com
name@gmail.com
name@xmsoftware.com 

```

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;

```

Answer: 
I would prefer `REGEXP_SUBSTR`

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

```

[**Demo**](https://dbfiddle.uk/?rdbms=oracle_11.2&fiddle=9b839374cab5ef56fe1f3e39a1a2345d)
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 'name@yahoo.com' FROM DUAL
  3           UNION ALL
  4           SELECT 'name@msn.com' FROM DUAL
  5           UNION ALL
  6           SELECT 'name@gmail.com' FROM DUAL
  7           UNION ALL
  8           SELECT 'name@xmsoftware.com ' 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
-------------------- --------------------
name@yahoo.com       yahoo
name@msn.com         msn
name@gmail.com       gmail
name@xmsoftware.com  xmsoftware

SQL>

```