SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Parsing out email address in FROM field


Parsing out email address in FROM field

Author
Message
vu_tran764
vu_tran764
SSC Rookie
SSC Rookie (32 reputation)SSC Rookie (32 reputation)SSC Rookie (32 reputation)SSC Rookie (32 reputation)SSC Rookie (32 reputation)SSC Rookie (32 reputation)SSC Rookie (32 reputation)SSC Rookie (32 reputation)

Group: General Forum Members
Points: 32 Visits: 59
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
Sean Lange
Sean Lange
SSC Guru
SSC Guru (59K reputation)SSC Guru (59K reputation)SSC Guru (59K reputation)SSC Guru (59K reputation)SSC Guru (59K reputation)SSC Guru (59K reputation)SSC Guru (59K reputation)SSC Guru (59K reputation)

Group: General Forum Members
Points: 59646 Visits: 17947
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 Modens 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)
Lowell
Lowell
SSC Guru
SSC Guru (68K reputation)SSC Guru (68K reputation)SSC Guru (68K reputation)SSC Guru (68K reputation)SSC Guru (68K reputation)SSC Guru (68K reputation)SSC Guru (68K reputation)SSC Guru (68K reputation)

Group: General Forum Members
Points: 68218 Visits: 40898
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
--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!
vu_tran764
vu_tran764
SSC Rookie
SSC Rookie (32 reputation)SSC Rookie (32 reputation)SSC Rookie (32 reputation)SSC Rookie (32 reputation)SSC Rookie (32 reputation)SSC Rookie (32 reputation)SSC Rookie (32 reputation)SSC Rookie (32 reputation)

Group: General Forum Members
Points: 32 Visits: 59
Thank you so much to both of you!. It works perfectly. Much appreciated!
vu_tran764
vu_tran764
SSC Rookie
SSC Rookie (32 reputation)SSC Rookie (32 reputation)SSC Rookie (32 reputation)SSC Rookie (32 reputation)SSC Rookie (32 reputation)SSC Rookie (32 reputation)SSC Rookie (32 reputation)SSC Rookie (32 reputation)

Group: General Forum Members
Points: 32 Visits: 59
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
Lowell
Lowell
SSC Guru
SSC Guru (68K reputation)SSC Guru (68K reputation)SSC Guru (68K reputation)SSC Guru (68K reputation)SSC Guru (68K reputation)SSC Guru (68K reputation)SSC Guru (68K reputation)SSC Guru (68K reputation)

Group: General Forum Members
Points: 68218 Visits: 40898
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
--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!
Sean Lange
Sean Lange
SSC Guru
SSC Guru (59K reputation)SSC Guru (59K reputation)SSC Guru (59K reputation)SSC Guru (59K reputation)SSC Guru (59K reputation)SSC Guru (59K reputation)SSC Guru (59K reputation)SSC Guru (59K reputation)

Group: General Forum Members
Points: 59646 Visits: 17947
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 Modens 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)
CapnHector
CapnHector
Hall of Fame
Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)

Group: General Forum Members
Points: 3083 Visits: 1789
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
vu_tran764
vu_tran764
SSC Rookie
SSC Rookie (32 reputation)SSC Rookie (32 reputation)SSC Rookie (32 reputation)SSC Rookie (32 reputation)SSC Rookie (32 reputation)SSC Rookie (32 reputation)SSC Rookie (32 reputation)SSC Rookie (32 reputation)

Group: General Forum Members
Points: 32 Visits: 59
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!
CapnHector
CapnHector
Hall of Fame
Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)

Group: General Forum Members
Points: 3083 Visits: 1789
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
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search