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

Can you show an example of what the data looks like?


Something like?

John Smith jsmith@hotmail.com (555)555-5555

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))

Select allOpen in new window

refined one:

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

Select allOpen in new window

As Gallatin said please show some examples and teh o/p u expected?

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 :)

Have you tried mine?

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,

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	

Select allOpen in new window

Ralmada, that's nearly it, it returns the email address dropping the stuff in front but also returns everything after it also :)

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	

Select allOpen in new window

Thanks for this, worked great and taught me a chunk more about SQL string handling!