• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 245
  • Last Modified:

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.
0
dgloveruk
Asked:
dgloveruk
1 Solution
 
Nathan RileyFounder/CTOCommented:
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
 
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
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
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
 
ralmadaCommented:
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
 
dgloverukAuthor Commented:
Thanks for this, worked great and taught me a chunk more about SQL string handling!
0

Featured Post

Prep for the ITIL® Foundation Certification Exam

December’s Course of the Month is now available! Enroll to learn ITIL® Foundation best practices for delivering IT services effectively and efficiently.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now