| email |
+---------------------------------+
| [email protected] |
| [email protected] [email protected] |
+---------------------------------+
I tried the below and it is working for the 1st row
substr(email,instr(email,'@',1,1))
But for 2nd row on a same cell we have 2 domain id. Now, how to retrieve the output as below
gmail.com
gmail.com
yahoo.com
For example:
SQL> with test (email) as
2 (select '[email protected]' from dual union
3 select '[email protected] [email protected]' from dual
4 )
5 select ltrim(regexp_substr(email, '@(\w+\.\w+)', 1, column_value), '@') res
6 from test,
7 table(cast(multiset(select level from dual
8 connect by level <= regexp_count(email, '@' )
9 ) as sys.odcinumberlist));
RES
-----------------------------------------------------------------------------------
gmail.com
gmail.com
yahoo.com
SQL>
You can use REGEXP_SUBSTR, for Example:
SELECT REGEXP_SUBSTR ('[email protected]', '(\@).*') from DUAL
Result: @outlook.com
You might try something like the following. I'm assuming you have a primary key column or columns on the table that also has the email column:
SELECT id, TRIM('@' FROM REGEXP_SUBSTR(email, '@\S+', 1, LEVEL))
FROM mytable
CONNECT BY REGEXP_SUBSTR(email, '@\S+', 1, LEVEL) IS NOT NULL
-- If you have a composite key, mention all of the columns here:
AND PRIOR id = id
AND PRIOR SYS_GUID() IS NOT NULL;
See SQL Fiddle here (ignore the schema!).
Now the above query will get all results, including duplicates, so if a value in the email column looks like this: [email protected] [email protected] [email protected] then gmail.com will be returned twice for that primary key value. Simply replace the SELECT with SELECT DISTINCT to get around that issue (if you want to get around it).