SQL Query help

Hi EE,

I have a SQL table that looks like this :

AutoID           Department           Email
   1                      Marketing             phil@microsoft.com;pi@microsoft.com;xa@microsoft.com;abcde@hotmail,com
...........
......
....


how can I do a script that will get all rows that contains "Semi colon separated" and only Marketing department, to do Insert into the table to have one row for one email ?
so it wold be :

1            Marketing       phil@microsoft.com
2           Marketing        pi@microsoft.com
3         Marketing          xa@microsoft.com
and so son.


thanks for your help.
LVL 1
PhilippeRenaudAsked:
Who is Participating?
 
ste5anSenior DeveloperCommented:
There is a minor glitch. Try this instead:

CREATE TABLE #Sample
(
    ID INT,
    Department NVARCHAR(255),
    Email NVARCHAR(MAX)
);

INSERT INTO #Sample
VALUES  ( 1, 'Marketing', 'phil@microsoft.com;pi@microsoft.com;xa@microsoft.com;abcde@hotmail.com <a h>' );

WITH ToXml AS
    (
        SELECT  S.*,
                CAST('<a><![CDATA[' + REPLACE(S.Email, ';', ']]></a><a><![CDATA[') + ']]></a>' AS XML) AS XmlData
        FROM    #Sample S
    )
SELECT  S.*,
        A.EmailAddress.value('.', 'NVARCHAR(255)') AS EmailAddress
FROM    ToXml S
    CROSS APPLY S.XmlData.nodes('/a') A ( EmailAddress )

DROP TABLE #Sample;

Open in new window

1
 
slightwv (䄆 Netminder) Commented:
What database product and version?
0
 
PhilippeRenaudAuthor Commented:
the dB is  SQL Server 9.00.4060
0
Cloud Class® Course: MCSA MCSE Windows Server 2012

This course teaches how to install and configure Windows Server 2012 R2.  It is the first step on your path to becoming a Microsoft Certified Solutions Expert (MCSE).

 
ste5anSenior DeveloperCommented:
You should considering a upgrade. The new version have a built-in STRING_SPLIT() function. You can use XML for doing this:

CREATE TABLE #Sample
(
    ID INT,
    Department NVARCHAR(255),
    Email NVARCHAR(MAX)
);

INSERT INTO #Sample
VALUES  ( 1, 'Marketing', 'phil@microsoft.com;pi@microsoft.com;xa@microsoft.com;abcde@hotmail.com <a h>' );

WITH ToXml AS
    (
        SELECT  S.*,
                CAST('<a>' + REPLACE(REPLACE(REPLACE(S.Email,'>','|gt|'), '<', '|lt|'), ';', '</a><a>') + '</a>' AS XML) AS XmlData
        FROM    #Sample S
    )
SELECT  S.*,
        REPLACE(REPLACE(A.EmailAddress.value('.', 'NVARCHAR(255)'), '|gt|', '>'), '|lt|', '<') AS EmailAddress
FROM    ToXml S
    CROSS APPLY S.XmlData.nodes('/a') A ( EmailAddress )

DROP TABLE #Sample;

Open in new window

0
 
PhilippeRenaudAuthor Commented:
They will do an upgrade around May. But until now I am stuck with that version.
Ok let me look at your script.
0
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
The article T-SQL:  Normalized data to a single comma delineated string and back is a full demo with code on how to pull this off, and for this version of SQL Server there is not an easy answer.  

Good luck.
0
 
PhilippeRenaudAuthor Commented:
thx
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.