October 22, 2012 at 12:50 pm
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:
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
October 22, 2012 at 1:04 pm
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 http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
October 22, 2012 at 1:06 pm
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
October 23, 2012 at 10:04 am
Thank you so much to both of you!. It works perfectly. Much appreciated!
October 26, 2012 at 10:39 am
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
October 26, 2012 at 10:51 am
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
October 26, 2012 at 11:04 am
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 http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
October 26, 2012 at 4:13 pm
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 performance Issues see how we like them posted here: How to Post Performance Problems - Gail Shaw[/url]
Need to Split some strings? Jeff Moden's DelimitedSplit8K[/url]
Jeff Moden's Cross tab and Pivots Part 1[/url]
Jeff Moden's Cross tab and Pivots Part 2[/url]
October 26, 2012 at 4:15 pm
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!
October 26, 2012 at 4:23 pm
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 performance Issues see how we like them posted here: How to Post Performance Problems - Gail Shaw[/url]
Need to Split some strings? Jeff Moden's DelimitedSplit8K[/url]
Jeff Moden's Cross tab and Pivots Part 1[/url]
Jeff Moden's Cross tab and Pivots Part 2[/url]
October 26, 2012 at 4:26 pm
Hi Sean,
Instead of using:
-----------------------------------------------------------------------------------------------------
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>'
)
------------------------------------------------------------------------------------------------------
1. Is there a way I can select all the values in the [to] from tbldoc? How am I union all the values in the [TO] field? I tried (Select [to] from tbldoc) but I got no lucks Thanks!
2. The sql server can't find this dbo.DelimitedSplit8K function. Should I add it in first? and how do I do that? Thanks! This is the same question I have for Lowell. PLease ignore this if Lowell already gave me the answer.
Thanks so much Sean! You are awesome!
October 26, 2012 at 4:33 pm
Hi capnhector,
Thank you for your explanation. I am getting the picture now. I think I need to add the function in first. Without doing so, I can't really tell if I want to have all values in the [TO] field to work.
Thanks again,
October 26, 2012 at 5:58 pm
Hi Sean,
I figured out what the dbo.DelimitedSplit8K function is and have it run successfully.
with cte as
(
select 1 as listid, [To] as tolist from tblDoc where [To]IS not null AND [To] LIKE '%.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
What I got for this [TO] field is a row of all the email addresses, rather they are supposed to be in their original rows. Let me know your thought on this.
Thanks,
October 26, 2012 at 6:16 pm
i see where the error is.
in your initial cte you have the following
with cte as
(
select 1 as listid, [To] as tolist from tblDoc where [To]IS not null AND [To] LIKE '%.COM%'
)
in Lowell's cte the SELECT 1 as ListID is to give each row an id (you will notice it changes for each [to] string). does your table has some sort of ID like EmailID or something that uniquely identifies each to string? if you do use that instead. otherwise you will need to use ROW_NUMBER() to get something unique for each row for the correlated sub query in the STUFF().
For performance Issues see how we like them posted here: How to Post Performance Problems - Gail Shaw[/url]
Need to Split some strings? Jeff Moden's DelimitedSplit8K[/url]
Jeff Moden's Cross tab and Pivots Part 1[/url]
Jeff Moden's Cross tab and Pivots Part 2[/url]
October 27, 2012 at 9:30 am
Thank you so much Capnhector. That makes a lot of senses now. I have the ID field, which is unique. I have to take out the group by clause. and add LTRM() and add a space so that it will return for every rows that need special treatments and add a space between two emails and trim out leading blank space. This works perfectly for me. The updated codes is below.
with cte as
(
select ID as listid, [To] as tolist from tblDoc p where [To]IS not null AND [To] LIKE '%.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,
LTRIM(Stuff((select '; ' + Item from Parse2 p2 where p2.ListID = p1.ListID FOR XML PATH('')), 1, 1, ' '))
from Parse2 p1
--group by ListID
Viewing 15 posts - 1 through 15 (of 19 total)
You must be logged in to reply to this topic. Login to reply