Home » SQL & PL/SQL » SQL & PL/SQL » Regular Expressions (Oracle 11g)
Regular Expressions [message #598360] Mon, 14 October 2013 05:39 Go to next message
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
Re: Regular Expressions [message #598362 is a reply to message #598360] Mon, 14 October 2013 06:00 Go to previous messageGo to next message
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.

Re: Regular Expressions [message #598363 is a reply to message #598360] Mon, 14 October 2013 06:00 Go to previous messageGo to next message
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

icon3.gif  Re: Regular Expressions [message #598366 is a reply to message #598360] Mon, 14 October 2013 06:21 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
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".

Re: Regular Expressions [message #598367 is a reply to message #598366] Mon, 14 October 2013 06:22 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
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 Go to previous messageGo to next message
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

Re: Regular Expressions [message #598493 is a reply to message #598372] Tue, 15 October 2013 05:48 Go to previous messageGo to next message
sivakumar121
Messages: 29
Registered: April 2009
Junior Member
Thank you so much to all -

DrabJay - The query you sent works perfectly - still trying to understanding the query logic. Thanks again.

Re: Regular Expressions [message #598502 is a reply to message #598493] Tue, 15 October 2013 07:11 Go to previous messageGo to next message
rommy144
Messages: 1
Registered: October 2013
Junior Member
Wow... It is the great post, it provides lot of new knowledge to me, thanks for sharing the nice post.
Re: Regular Expressions [message #598506 is a reply to message #598502] Tue, 15 October 2013 07:20 Go to previous message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

So you can also do the search I mentioned and click on the link I gave.

Previous Topic: dynamic column creation
Next Topic: Selectim with rownum=1 taking time
Goto Forum:
  


Current Time: Thu Mar 28 06:24:42 CDT 2024