mradul mradul - 9 days ago 5
SQL Question

Fetch email address from NVARCHAR2 DATATYPE

I have a table in which there is a column of NVARCHAR2 datatype which holds a string.
The string contains some Email Ids which I require to fetch in a comma separated manner.

Below is the test data --

create table nvarchar2_email (email_reject nvarchar2(1000));

insert into nvarchar2_email values ('com.wm.app.b2b.server.ServiceException: javax.mail.SendFailedException: Invalid Addresses; nested exception is:
com.sun.mail.smtp.SMTPAddressFailedException: 550 5.1.1 <manoj.dalai@gmail.com>: Recipient address rejected: User unknown in virtual alias table;
nested exception is:
com.sun.mail.smtp.SMTPAddressFailedException: 550 5.1.1 <santoshi.k@gmail.com>: Recipient address rejected: User unknown in virtual alias table
nested exception is:
com.sun.mail.smtp.SMTPAddressFailedException: 550 5.1.1 <biswajit-kumar.p@gmail.com>: Recipient address rejected: User unknown in virtual alias table');

insert into nvarchar2_email values ('com.wm.app.b2b.server.ServiceException: javax.mail.SendFailedException: Invalid Addresses; nested exception is:
com.sun.mail.smtp.SMTPAddressFailedException: 550 5.1.1 <manoj.dalai@gmail.com>: Recipient address rejected: User unknown in virtual alias table;
nested exception is:
com.sun.mail.smtp.SMTPAddressFailedException: 550 5.1.1 <santoshi.k@gmail.com>: Recipient address rejected: User unknown in virtual alias table');


I am trying to use the below SQL but it is repeating the Email Ids !!

select email_rejetc, listagg(REGEXP_substr (email_rejetc,'[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Za-z]{2,4}', 1,level), ',') within group (order by email_rejetc) invalid_email
from nvarchar2_email
connect by level <= REGEXP_count (email_rejetc,'[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Za-z]{2,4}')
group by EMAIL_REJETC


Here the required output is like

manoj.dalai@gmail.com,santosh.k@gmail.com,biswajit-kumar@gmail.com


Number of emails can VARY in different rows of the table;

My DB is :
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production

Answer
select  (select          listagg (regexp_substr(cast(e.email_reject as varchar2(1000)),'<(.*?@.*?)>',1,level,'',1),',') 
                             within group (order by e.email_reject)
         from            dual
         connect by      level <= regexp_count (e.email_reject,'<.*?@.*?>')
         )       as emails                         

from     nvarchar2_email e
;

P.s.

There seem to be an issue with regexp_substr and nvarchar that causes each character in the result to be preceded by \0.
Tested on Oracle Database 11g Express Edition Release 11.2.0.2.0 - 64bit Production

Comments