How to pick out an email address in amongst a varchar(255) string using SQL Server 2000

I have a field in a table which is of type varchar(255) and may contain an email address in amongst other text.  The email address will have a space before and after it.  I wish to select/display the email address.  There may be more than one email address, I would prefer to display only the first occurence in this circumstance.
dgloverukAsked:
Who is Participating?
 
ralmadaConnect With a Mentor Commented:
Sorry, this is the correct one:
select CASE WHEN charindex('@', yourfield) <> 0 then
		right(
			left(yourfield, charindex('@', yourfield) - 1), 
 
			charindex(' ', reverse(left(yourfield, charindex('@', yourfield) - 1))) 
		) +
		substring(yourfield, 
			charindex('@', yourfield) , 
			charindex(' ', yourfield, charindex('@', yourfield)) - charindex('@', yourfield)
		)
	    ELSE ' ' END as youremail
from yourtable	

Open in new window

0
 
Nathan RileyFounderCommented:
Can you show an example of what the data looks like?


Something like?

John Smith jsmith@hotmail.com (555)555-5555
0
 
ralmadaCommented:
Please give this a try:
select right(
		left(yourfield, charindex('@', yourfield) - 1), 
		charindex(' ', reverse(left(yourfield, charindex('@', yourfield) - 1))) 
	) +
	substring(yourfield, charindex('@', yourfield) + 1, charindex(' ', yourfield, charindex('@', yourfield) + 1))

Open in new window

0
The 14th Annual Expert Award Winners

The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!

 
ralmadaCommented:
refined one:

select right(
		left(yourfield, charindex('@', yourfield) - 1), 
		charindex(' ', reverse(left(yourfield, charindex('@', yourfield) - 1))) 
	) +
	substring(yourfield, charindex('@', yourfield) , charindex(' ', yourfield, charindex('@', yourfield)))

Open in new window

0
 
tcs224694Commented:
As Gallatin said please show some examples and teh o/p u expected?
0
 
dgloverukAuthor Commented:
This is an example...

I Parkinson - E-Mail Subject: FW: CV Basket CV: ian parkinson
From: victoria.stout@first-careers.co.uk (Victoria Stout)

Cheers my dears!

-----Original Message-----
From: Jobsite CV Basket [mailto:null@jobsite.co.uk]
Sent: 11 May 2004 15:13
To: v.....

I'de want the first email : victoria.stout@first-careers.co.uk to be returned

I'll give the suggestions a try :)
0
 
ralmadaCommented:
Have you tried mine?
0
 
dgloverukAuthor Commented:
Hi ralmada,
I think your suggestion will work but can you enhance it so it can cope with an empty/null 'yourstring', returning nothing instead.
Regards,
0
 
ralmadaCommented:
Sure. Here you go
select CASE WHEN charindex('@', yourfield) <> 0 then
		right(
			left(yourfield, charindex('@', yourfield) - 1), 
			charindex(' ', reverse(left(yourfield, charindex('@', yourfield) - 1))) 
		) +
		substring(yourfield, charindex('@', yourfield) , charindex(' ', yourfield, charindex('@', yourfield)))
	    ELSE ' ' END as youremail
from yourtable	

Open in new window

0
 
dgloverukAuthor Commented:
Ralmada, that's nearly it, it returns the email address dropping the stuff in front but also returns everything after it also :)
0
 
ralmadaCommented:
try this

select CASE WHEN charindex('@', yourfield) <> 0 then
		right(
			left(yourfield, charindex('@', yourfield) - 1), 
 
			charindex(' ', reverse(left(yourfield, charindex('@', yourfield) - 1))) 
		) +
		substring(yourfield, 
			charindex('@', yourfield) , 
			charindex(' ', yourfield, charindex('@', yourfield) - charindex('@', yourfield))
		)
	    ELSE ' ' END as youremail
from yourtable	

Open in new window

0
 
dgloverukAuthor Commented:
Thanks for this, worked great and taught me a chunk more about SQL string handling!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.