Search code examples
sqloracle-databaseplsqlregexp-replace

REGEXP_REPLACE to replace emails in a list except a specific domain

I am novice to regular expressions. I am trying to remove emails from a list which do not belong to a specific domain.

for e.g. I have a below list of emails:

John@yahoo.co.in , Jacob@gmail.com, Bob@rediff.com,
Lisa@abc.com, sam@gmail.com , rita@yahoo.com

I need to get only the gmail ids:

Jacob@gmail.com, sam@gmail.com

Please note we may have spaces before the comma delimiters. Appreciate any help!

Solution

  • Rather than suppress the emails not matching a particular domain (in your example, gmail.com), you might try getting only those emails that match the domain:

    WITH a1 AS (
    SELECT 'John@yahoo.co.in , Jacob@gmail.com, Bob@rediff.com,Lisa@abc.com, sam@gmail.com , rita@yahoo.com' AS email_list FROM dual
)
SELECT LISTAGG(TRIM(email), ',') WITHIN GROUP ( ORDER BY priority )
  FROM (
    SELECT REGEXP_SUBSTR(email_list, '[^,]+@gmail.com', 1, LEVEL, 'i') AS email
         , LEVEL AS priority
      FROM a1
   CONNECT BY LEVEL <= REGEXP_COUNT(email_list, '[^,]+@gmail.com', 1, 'i')
);

    That said, Oracle is probably not the best tool for this (do you have these email addresses stored as a list in a table somewhere? If so then @GordonLinoff's comment is apt - fix your data model if you can).