OraFAQ Forum: SQL & PL/SQL » Regular Expressions

Home » SQL & PL/SQL » SQL & PL/SQL » Regular Expressions (Oracle 11g)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Regular Expressions [message #598360]

Mon, 14 October 2013 05:39

sivakumar121
Messages: 29
Registered: April 2009

Junior Member

Hi

Can some one help me with the code to extract email id (full email id) from a string/text using Oracle Regular Expressions.

Example -

"This is my String with email abc.efg@hij.com to test"
O/p - abc.efg@hij.com

Report message to a moderator

Re: Regular Expressions [message #598362 is a reply to message #598360]

Mon, 14 October 2013 06:00

pablolee
Messages: 2882
Registered: May 2007
Location: Scotland

Senior Member

Regular expressions are not my thing (I really need to sit down one of these days and spend some time on them.) however, to save you some time, I'll point out something that one of the folk who can help you, will almost certainly ask for. i.e. The rules. You need to tell us what rules dictate the start and end of an email.

Report message to a moderator

Re: Regular Expressions [message #598363 is a reply to message #598360]

Mon, 14 October 2013 06:00

Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web

Senior Member

Give it a try http://www.orafaq.com/node/2404

sivakumar121 wrote on Mon, 14 October 2013 16:09

"This is my String with email abc.efg@hij.com to test"
O/p - abc.efg@hij.com

If you are sure that the email id pattern will be always of the form "abc.efg@hij.com", then my demo should work for you.

SQL> SELECT regexp_substr('This is my String with email abc.efg@hij.com to test'
, '\w...\w+@\w+\.\w+') email_ids from dual;

EMAIL_IDS
---------------
abc.efg@hij.com

Regards,
Lalit

[Updated on: Thu, 06 March 2014 03:14] by Moderator

Report message to a moderator

Re: Regular Expressions [message #598366 is a reply to message #598360]

Mon, 14 October 2013 06:21

Michel Cadot
Messages: 68767
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

There is a topic somewhere that shows how to validate an email address, it'd be easy to adapt it to extract an email address.
Search for "validate email address".

Report message to a moderator

Re: Regular Expressions [message #598367 is a reply to message #598366]

Mon, 14 October 2013 06:22

Michel Cadot
Messages: 68767
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

There is also this.

[Updated on: Mon, 14 October 2013 06:24]

Report message to a moderator

Re: Regular Expressions [message #598372 is a reply to message #598367]

Mon, 14 October 2013 07:01

DrabJay
Messages: 32
Registered: May 2013

Member

Here is a version which should match the vast majority of email addresses. It doesn't conform to the RFC but should extract most email addresses that are actually in use.

SELECT regexp_substr('This is my String with email abc.efg@hij.com to test',
                     '((([A-Za-z0-9_.%+-])+)[@](([a-z0-9]|([a-z0-9][a-z0-9-]*[a-z0-9]))(\.([a-z0-9]|([a-z0-9][a-z0-9-]*[a-z0-9])))*))([^a-z0-9]|$)',
                     1,
                     1,
                     'c',
                     1) email
FROM dual

Report message to a moderator