Home Forums SQL Server 2008 SQL Server 2008 - General Parsing out email address in FROM field RE: Parsing out email address in FROM field

  • October 26, 2012 at 4:23 pm

    #1553441

    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[/url] 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[/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]