Getting the e-mail address out of a string.

Question

Post reply

Getting the e-mail address out of a string.

Justyna

Mr or Mrs. 500

Points: 594
More actions
June 2, 2006 at 12:52 pm

#168668

Hi guys!
My brain is fried to the point where I can't even seem to tie my own shoe laces. Help me out...
I have a column in a SQL database that has data like this:
Name: Joe Smith Phone: 333-333-3333 E-mail: joe@joesmith.com Notes: Blah Blah Blah
How the heck do I parse out just the e-mail address using a T-SQL query?
Much appreciated.
J

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

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

PW-201837 SSC-Insane Points: 20805 More actions · Answer 1

Is it always after the text "E-mail:" and before the text " Notes:" ?

If yes, then read BOL on the CharIndex() and Substring() functions.

vadba SSChampion Points: 11133 More actions · Answer 2

Here one way. Create UDF to parse a single string, then you can use the UDF in a query.

--EXAMPLE query, run after the UDF is created.

-- SELECT dbo.fGetEMail('Name: Joe Smith Phone: 333-333-3333 E-mail:

joe@joesmith.comNotes:') AS email_address

-----------------------------------------------------------------------

GO

DROP FUNCTION dbo.fGetEMail

GO

CREATE FUNCTION dbo.fGetEMail

(

@s-2 varchar(1000)

)

RETURNS varchar(1000)

AS

BEGIN

DECLARE @email varchar(1000)

, @pos1 int

, @pos2 int

--------------------------------------------

-- some error checking

--------------------------------------------

SET @pos1 = CharIndex('E-mail:', @s-2)

IF @pos1 < 1

RETURN null

SET @pos2 = CharIndex('Notes:', @s-2)

IF @pos2 < 1

RETURN null

IF @pos1 > @pos2

RETURN null

--------------------------------------------

SET @pos1 = @pos1 + Len('E-mail:')

SET @email = RTrim(LTrim(Substring(@s, @pos1 , @pos2 - @pos1)))

RETURN @email

END --FUNCTION

Dan Murray Right there with Babe Points: 779 More actions · Answer 3

T-Sql is notouriously bad at parsing text. Just the bare tools are given and nothing more.

That being said here an approach that can be used in line.

set

nocount on

declare

@STR as varchar(100)

declare

@val as int

declare

@val2 as int

set

@STR='E-mail: joe@joesmith.com Notes: Blah Blah Blah'

set

@val=patindex('% %@% %',@STR)+1

set

@Val2=patindex('% %',substring(@STR,@val,99))-1

print

@val

print

@val2

print

substring(@STR,@val,@Val2)

--InLine

select

substring(@STR,patindex('% %@% %',@STR)+1,patindex('% %',substring(@STR,patindex('% %@% %',@STR)+1,99))-1)

Ugly , but it works.

Hope this helps.

Justyna Mr or Mrs. 500 Points: 594 More actions · Answer 4

Thanks for all your help and suggestions.

I ended up doing sort of a combo using substring and replace.

Job steps

1) select substring(vfdata, charindex('Email:', vfdata), len(vfdata))

2) update HAZVENDCONTACTS

set email = left(email, charindex('Notes:', email) - 1)

3) update HAZVENDCONTACTS

set email = ltrim(replace(email, 'Notes:', ''))

Pretty simple and does the trick.