extract email address from text column

Question

Post reply

extract email address from text column

vijju2k

SSCommitted

Points: 1531
More actions
December 17, 2003 at 3:56 pm

#85195

Hi,
I have a text column in my SQL database table which stores comments from users (including email addresses). Is there a way I can extract only EMAIL address out of this column and store it into another column (in the same table or another table)?
If this question is already asked, I apologize for re-posting. Please redirect me to the old one, I couldnt locate it.
thank you all in advance,
v

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

gundapaneni Mr or Mrs. 500 Points: 593 More actions · Answer 1

Hi,

Can U provide some sampe data...so that I can build a query.

Madhu

Frank Kalis SSC Guru Points: 111183 More actions · Answer 2

Yes, without sample data it's difficult.

I have done something similar in a small app outside SQL Server

Basically you need to search for the @ sign, then take all characters to the left of it till it is a blank and again all characters to the right of @ till it is a blank.

Frank

http://www.insidesql.de

http://www.familienzirkus.de

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

David Burrows SSC Guru Points: 65143 More actions · Answer 3

If your data conforms to Franks post.

SELECT 

STUFF(SUBSTRING(textcol,1,CHARINDEX(' ',textcol,CHARINDEX('@', textcol))-1),1, 

CHARINDEX('@', textcol) - CHARINDEX(' ',

REVERSE(SUBSTRING(textcol,1,CHARINDEX('@', textcol)-1))),'') 

FROM

Edited by - davidburrows on 12/18/2003 06:43:00 AM

Far away is close at hand in the images of elsewhere.
Anon.

David Burrows SSC Guru Points: 65143 More actions · Answer 4

or

SELECT

SUBSTRING(SUBSTRING(textcol,1,CHARINDEX(' ',textcol,CHARINDEX('@', textcol))-1),

CHARINDEX('@', textcol) - CHARINDEX(' ',

REVERSE(SUBSTRING(textcol,1,CHARINDEX('@', textcol)-1))) + 1,255)

FROM

Far away is close at hand in the images of elsewhere.
Anon.

vijju2k SSCommitted Points: 1531 More actions · Answer 5

Thank you guys for responding to my query, the sample data in this column looks like:

comments;l;comments, more comments

NULL

email::test@microsoft.com;comments

email: test@microsoft.com;;;comments

NULL

test@microsoft.com

the data is basically a mix of everything.

thank you,

v

David: I am working with your suggestion, but am getting an error saying "Invalid length parameter passed to substring function". I am assuming it has something to do with null data. But I dont know.