Extract email from field using Oracle Regexp

Question

I want to get e-mail formed texts in a field. I have tried sql below but no luck. See SqlFiddle. Removing ^ and $ from regexp not working too.

WITH TEST_DATA AS (
  SELECT '[email protected]' AS EMAIL FROM DUAL UNION ALL 
  SELECT 'mail [email protected]' FROM DUAL UNION ALL           
  SELECT 'mail [email protected] sent' FROM DUAL UNION ALL                
  SELECT '[email protected] sent count 23' FROM DUAL UNION ALL          
  SELECT 'mail already sent to [email protected] and [email protected]' FROM DUAL UNION ALL                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                   
  SELECT '[email protected] sent count 23' FROM DUAL             
)SELECT REGEXP_SUBSTR(EMAIL,'^[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Za-z]{2,4}$') MAIL
 FROM TEST_DATA;

Expected output for this dataset

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

Any help appreciated.

Answer 1

If you want to extract multiple mail ids in a single column, you can use REGEXP_REPLACE function.

Assuming all the ids in your data are valid ones,

REGEXP_REPLACE (EMAIL, '(\w+@\w+\.\w+ ?)|(.)', '\1')

This removes all other text except for mail ids that are separated by at least a space.

You can then remove any trailing spaces and add comma to separate multiple ids.

REPLACE (TRIM (REGEXP_REPLACE (EMAIL, '(\w+@\w+\.\w+ ?)|(.)', '\1')),
            ' ',
            ', ')

Example:

WITH TEST_DATA
     AS (SELECT '[email protected]' AS EMAIL FROM DUAL
         UNION ALL
         SELECT 'mail [email protected]' FROM DUAL
         UNION ALL
         SELECT 'mail [email protected] sent to [email protected] and [email protected]' FROM DUAL
         UNION ALL
         SELECT '[email protected] sent count 23 and [email protected]' FROM DUAL
         UNION ALL
         SELECT 'mail already sent to [email protected] and [email protected]' FROM DUAL
         UNION ALL
         SELECT '[email protected] sent count 23' FROM DUAL)
SELECT REPLACE (TRIM (REGEXP_REPLACE (EMAIL, '(\w+@\w+\.\w+ ?)|(.)', '\1')),
                ' ',
                ', ')
          MAIL
  FROM TEST_DATA;

MAIL
-----------------------------
[email protected]
[email protected]
[email protected], [email protected], [email protected]
[email protected], [email protected]
[email protected], [email protected]
[email protected]