how to extract an email from the FROM field

[vinikatyal]

What i want to do is extract an email from the FROM field which is stored in the following format
"vini katyal" <[e-mail]> varchar field

[patjones]

I take it that you're looking to do this via a simple SELECT query?

Pat

[ck9663]

You need to parse that column. What do you have so far?



~~ CK

[patjones]

You could try something like this. It assumes that you have a table of digits (tblDigits) from 1 to 100, under a column name digit.

SELECT SUBSTRING(tblContacts.fldEmail, tA.digit + 1, tB.digit - tA.digit - 1)
FROM tblContacts, tblDigits tA, tblDigits tB
WHERE tblContacts.fldEmail IS NOT NULL
      AND
      tB.digit - tA.digit > 0
      AND
      tB.digit - tA.digit < LEN(tblContacts.fldEmail)
      AND
      SUBSTRING(tblContacts.fldEmail, tA.digit, 1) = '<'
      AND
      SUBSTRING(tblContacts.fldEmail, tB.digit, 1) = '>';

I'm sure there are other possibilities.

Pat

[clvandyke]

DECLARE @Email varchar(50)
SET @Email = '"vini katyal" <[e-mail]>'
SELECT
SUBSTRING(@Email,
charindex('<',@Email),
LEN(@Email)) as [email]

[patjones]

@clvandyke: Good stuff! I cut and pasted it into my SSMS window and it works nicely. It would be great if the OP can weigh in.

Pat

[nbiswas]

Try this

Declare @str varchar(50) = '"vini katyal" <[e-mail]>'

Solution 1
-------------

Select 
        Name  = Left(@str, PATINDEX('%<%',@str)-1)
        ,Domain = Right(@str,Len(@str)-PATINDEX('%<%',@str)+1)
        ,Email = @str
 

Solution 2

Select 
        Name  = Left(@str, CHARINDEX('<',@str)-1)
        ,Domain = Right(@str,Len(@str)-CHARINDEX('<',@str)+1)
        ,Email = @str
 

Solution 3

Select 
        Name  = REVERSE(STUFF(REVERSE(@str),1,PATINDEX('%<%',REVERSE(@str)),''))
        ,Domain = STUFF(@str,1,PATINDEX('%<%',@str)-1,'')
        ,Email = @str
 

Solution 4

Select 
        Name  = SUBSTRING(@str,1,CHARINDEX('<',@str,1)-1)
        ,Domain = Right(@str,Len(@str)-CHARINDEX('<',@str)+1)
        ,Email = @str
 

In all the four cases the result is as under

Name    Domain    Email
"vini katyal"     <[e-mail]>    "vini katyal" <[e-mail]>

Hope it helps