Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Parsing out email address in FROM field Expand / Collapse
Author
Message
Posted Monday, October 22, 2012 12:50 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, August 26, 2013 12:46 AM
Points: 12, Visits: 40
Hello,

Can someone please help me to trouble-shoot my query?

Here is the scenario. I have FROM field in my table and they contain something like this.

FROM
John Doe1 <John.Doe1@abcd.com>
John Doe2 <John.Doe2@abcd.com>
John Doe3 <John.Doe3@abcd.com>

I just need to take out the email address just like this:

John.Doe1@abcd.com
John.Doe2@abcd.com
John.Doe3@abcd.com


I tried this method but it is not correct but getting close to finish:
SELECT SUBSTRING([From], CHARINDEX('<', [From])+1 , CHARINDEX('>', SUBSTRING([From], CHARINDEX('', [From]), 100)))
from tblDoc
where [From]IS not null

Can you please help me with this? Much Appreciated!
Thanks,
Vu





Post #1375677
Posted Monday, October 22, 2012 1:04 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 3:31 PM
Points: 11,949, Visits: 10,982
Like this?

create table #tblDoc
(
FromSender varchar(150)
)

insert #tblDoc
select 'John Doe1 <John.Doe1@abcd.com>' union all
select 'John Doe2 <John.Doe2@abcd.com>' union all
select 'John Doe3 <John.Doe3@abcd.com>' union all
select 'Some longer name <LongEmailAddressHere@LongDomainName.com>' union all
select 'Short <e@e.e>'


SELECT substring(FromSender, CHARINDEX('<', FromSender) + 1, CHARINDEX('>', FromSender) - CHARINDEX('<', FromSender) - 1)
from #tblDoc

drop table #tblDoc




_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1375682
Posted Monday, October 22, 2012 1:06 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 4:29 PM
Points: 12,741, Visits: 31,053
forme, a CTE that pulls out the charindexes makes it easier for me to understand.

With tblDoc([FROM])
AS
(
SELECT 'John Doe1 <John.Doe1@abcd.com>' UNION ALL
SELECT 'John Doe2 <John.Doe2@abcd.com>' UNION ALL
SELECT 'John Doe3 <John.Doe3@abcd.com>' UNION ALL
SELECT 'Lowell Mumble Mumble <Lowell@somedomain.com>'
),
tblDoc2
As
(
SELECT
CHARINDEX('<', [From]) As LeftBracket,
CHARINDEX('>', [From]) As RightBracket,
[FROM]
FROM tblDoc
)
SELECT SUBSTRING([From],LeftBracket +1,RightBracket - (LeftBracket +1)),*
FROM tblDoc2




Lowell

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #1375683
Posted Tuesday, October 23, 2012 10:04 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, August 26, 2013 12:46 AM
Points: 12, Visits: 40
Thank you so much to both of you!. It works perfectly. Much appreciated!
Post #1376161
Posted Friday, October 26, 2012 10:39 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, August 26, 2013 12:46 AM
Points: 12, Visits: 40
I may need your help again

I asked you for help with the From field however I am coning across another problem similar to this but this time it's a TO or CC field which they contain two or more email addresses. For example:

To
John Doe1 <John.Doe1@abc.com>; Mary Doe1 <Mary.Doe1@abc.com>
John Doe3 <John.Doe3@abc.com>; Mary Doe1 <Mary.Doe2@abc.org>

What we want to get should look like this:

To
John.Doe1@abc.com;Mary.Doe1@abc.com
John.Doe3@abc.com;Mary.Doe2@abc.org

Can you please help me with this problem?
Thanks,
Vu




Post #1377734
Posted Friday, October 26, 2012 10:51 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 4:29 PM
Points: 12,741, Visits: 31,053
well, if your data is split with semicolons or some known string,, then you could split it with the DelimitedSplit8K function.
www.sqlservercentral.com/articles/Tally+Table/72993/


With tblDoc([FROM])
AS
(
SELECT 'John Doe1 <John.Doe1@abcd.com>' UNION ALL
SELECT 'John Doe2 <John.Doe2@abcd.com>' UNION ALL
SELECT 'John Doe3 <John.Doe3@abcd.com>' UNION ALL
SELECT 'John Doe1 <John.Doe1@abc.com>; Mary Doe1 <Mary.Doe1@abc.com>' UNION ALL
SELECT 'John Doe3 <John.Doe3@abc.com>; Mary Doe1 <Mary.Doe2@abc.org>' UNION ALL
SELECT 'John Doe1 <John.Doe1@abc.com>; Mary Doe1 <Mary.Doe1@abc.com> ;Bill Doe1 <Billie.Doe1@abc.com>; Katie Doe1 <Katie.Doe1@abc.com>' UNION ALL
SELECT 'John Doe3 <John.Doe3@abc.com>; Mary Doe1 <Mary.Doe2@abc.org> ;Bill Doe1 <Billie.Doe1@abc.com>; ' UNION ALL --notice teh EXTRA semicolon
SELECT 'Lowell Mumble Mumble <Lowell@somedomain.com>'
),
SplitData
AS
(
SELECT myfunc.Item
FROM tblDoc
CROSS APPLY dbo.DelimitedSplit8K([FROM],';') myfunc
),
tblDoc2
As
(
SELECT
CHARINDEX('<', Item) As LeftBracket,
CHARINDEX('>', Item) As RightBracket,
Item
FROM SplitData
)
SELECT SUBSTRING(Item,LeftBracket +1,RightBracket - (LeftBracket +1)),*
FROM tblDoc2
WHERE LeftBracket < RightBracket





Lowell

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #1377742
Posted Friday, October 26, 2012 11:04 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 3:31 PM
Points: 11,949, Visits: 10,982
Well it looks like Lowell beat me to the punch on this one. I rolled my own version, which also uses the DelimitedSplit8K function.

;with cte as 
(
select 1 as ListID, 'John Doe1 <John.Doe1@abc.com>; Mary Doe1 <Mary.Doe1@abc.com>' as ToList union all
select 2, 'John Doe3 <John.Doe3@abc.com>; Mary Doe1 <Mary.Doe2@abc.org>Some longer name <LongEmailAddressHere@LongDomainName.com>;Short <e@e.e>' union all
select 3, 'Lowell Mumble Mumble <Lowell@somedomain.com>'
)
, Parse1 as
(
select *
from cte
cross apply dbo.DelimitedSplit8K(ToList, '<')
)
, Parse2 as
(
select p1.ListID, s.Item from parse1 p1
cross apply dbo.DelimitedSplit8K(Item, '>') s
where charindex('@', s.Item) > 0
)

select ListID,
Stuff((select ';' + Item from Parse2 p2 where p2.ListID = p1.ListID FOR XML PATH('')), 1, 1, ' ')
from Parse2 p1
group by ListID



_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1377751
Posted Friday, October 26, 2012 4:13 PM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Thursday, March 27, 2014 5:29 AM
Points: 945, Visits: 1,760
Sean Lange (10/26/2012)
Well it looks like Lowell beat me to the punch on this one. I rolled my own version, which also uses the DelimitedSplit8K function.

;with cte as 
(
select 1 as ListID, 'John Doe1 <John.Doe1@abc.com>; Mary Doe1 <Mary.Doe1@abc.com>' as ToList union all
select 2, 'John Doe3 <John.Doe3@abc.com>; Mary Doe1 <Mary.Doe2@abc.org>Some longer name <LongEmailAddressHere@LongDomainName.com>;Short <e@e.e>' union all
select 3, 'Lowell Mumble Mumble <Lowell@somedomain.com>'
)
, Parse1 as
(
select *
from cte
cross apply dbo.DelimitedSplit8K(ToList, '<')
)
, Parse2 as
(
select p1.ListID, s.Item from parse1 p1
cross apply dbo.DelimitedSplit8K(Item, '>') s
where charindex('@', s.Item) > 0
)

select ListID,
Stuff((select ';' + Item from Parse2 p2 where p2.ListID = p1.ListID FOR XML PATH('')), 1, 1, ' ')
from Parse2 p1
group by ListID



I like your double use of delimited split 8k. if i had the time tonight i would set up a test bed and run them both. might get around to it sunday night some time after i get some homework done for a course im taking.



For faster help in answering any problems Please read How to post data/code on a forum to get the best help - Jeff Moden for the best way to ask your question.

For performance Issues see how we like them posted here: How to Post Performance Problems - Gail Shaw

Need to Split some strings? Jeff Moden's DelimitedSplit8K
Jeff Moden's Cross tab and Pivots Part 1
Jeff Moden's Cross tab and Pivots Part 2

Jeremy Oursler
Post #1377895
Posted Friday, October 26, 2012 4:15 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, August 26, 2013 12:46 AM
Points: 12, Visits: 40
Thank you Lowell and Sean! Much appreciated!

This reply post is for Lowell. I am testing out your codes and I think I'm slightly not getting some part of the codes. For one instance: Rather selecting specific values, we like to get all the values in the [TO] field:

(Lowell's code)
-------------------------------------------------------------------
AS
(
SELECT 'John Doe1 <John.Doe1@abcd.com>' UNION ALL
SELECT 'John Doe2 <John.Doe2@abcd.com>' UNION ALL
SELECT 'John Doe3 <John.Doe3@abcd.com>' UNION ALL
SELECT 'John Doe1 <John.Doe1@abc.com>; Mary Doe1 <Mary.Doe1@abc.com>' UNION ALL
SELECT 'John Doe3 <John.Doe3@abc.com>; Mary Doe1 <Mary.Doe2@abc.org>' UNION ALL
SELECT 'John Doe1 <John.Doe1@abc.com>; Mary Doe1 <Mary.Doe1@abc.com> ;Bill Doe1 <Billie.Doe1@abc.com>; Katie Doe1 <Katie.Doe1@abc.com>' UNION ALL
SELECT 'John Doe3 <John.Doe3@abc.com>; Mary Doe1 <Mary.Doe2@abc.org> ;Bill Doe1 <Billie.Doe1@abc.com>; ' UNION ALL --notice teh EXTRA semicolon
SELECT 'Lowell Mumble Mumble <Lowell@somedomain.com>'
),
---------------------------------------------------------------------
Is there a way I can just select all values in the [TO] field instead of the above and Union All?

Additionally, I think myfunc.Item is not in the tbldoc and dbo.DelimitedSplit8K is an invalid name after I set it to run. Should this function be added to tbldoc? I am not familiar with this custom function. How can I have this function in the tbldoc?

Thank you Lowell! You are awesome!

Post #1377896
Posted Friday, October 26, 2012 4:23 PM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Thursday, March 27, 2014 5:29 AM
Points: 945, Visits: 1,760
vu_tran764 (10/26/2012)
Thank you Lowell and Sean! Much appreciated!

This reply post is for Lowell. I am testing out your codes and I think I'm slightly not getting some part of the codes. For one instance: Rather selecting specific values, we like to get all the values in the [TO] field:

(Lowell's code)
-------------------------------------------------------------------
AS
(
SELECT 'John Doe1 <John.Doe1@abcd.com>' UNION ALL
SELECT 'John Doe2 <John.Doe2@abcd.com>' UNION ALL
SELECT 'John Doe3 <John.Doe3@abcd.com>' UNION ALL
SELECT 'John Doe1 <John.Doe1@abc.com>; Mary Doe1 <Mary.Doe1@abc.com>' UNION ALL
SELECT 'John Doe3 <John.Doe3@abc.com>; Mary Doe1 <Mary.Doe2@abc.org>' UNION ALL
SELECT 'John Doe1 <John.Doe1@abc.com>; Mary Doe1 <Mary.Doe1@abc.com> ;Bill Doe1 <Billie.Doe1@abc.com>; Katie Doe1 <Katie.Doe1@abc.com>' UNION ALL
SELECT 'John Doe3 <John.Doe3@abc.com>; Mary Doe1 <Mary.Doe2@abc.org> ;Bill Doe1 <Billie.Doe1@abc.com>; ' UNION ALL --notice teh EXTRA semicolon
SELECT 'Lowell Mumble Mumble <Lowell@somedomain.com>'
),
---------------------------------------------------------------------
Is there a way I can just select all values in the [TO] field instead of the above and Union All?

Additionally, I think myfunc.Item is not in the tbldoc and dbo.DelimitedSplit8K is an invalid name after I set it to run. Should this function be added to tbldoc? I am not familiar with this custom function. How can I have this function in the tbldoc?

Thank you Lowell! You are awesome!



the section of code you pasted is where Lowell is building a CTE to hold the sample data. you can replace any refference to the CTE name with the sample data with your actual table and remove the cte holding the sample data. DelimitedSplit8K is a splitter function designed by Jeff Moden. the link to the article is in my signature. it is a very fast delimited list split but you will need to create the User Defined Function in order for Lowells code to work. and the myfunc.Item is because myfunc is the name Lowell gave when he was using delimited split 8k.


EDIT: hit the button to soon



For faster help in answering any problems Please read How to post data/code on a forum to get the best help - Jeff Moden for the best way to ask your question.

For performance Issues see how we like them posted here: How to Post Performance Problems - Gail Shaw

Need to Split some strings? Jeff Moden's DelimitedSplit8K
Jeff Moden's Cross tab and Pivots Part 1
Jeff Moden's Cross tab and Pivots Part 2

Jeremy Oursler
Post #1377900
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse