how to split column when meet special character

Question

I need to split the data in single column to multi column.
I have the data like this

ID       NAME                COMMENT
1        TEST      Reg: 0001 Inv: B0001 Cus: A0001 Br: F0001
2        TEST1     Reg: 0002 Inv: B0002 Cus: A0002 Br: F0002
3        TEST2     Reg: 0003 Inv: B0003 Cus: A0003 Br: F0003
4        TEST3     Reg: 0004 Inv: B0004 Cus: A0004 Br: F0004
5        TEST4     Reg: 0005 Inv: B0005 Cus: A0005 Br: F0005
5        TEST4     Reg: 00010 Inv: B00010 Cus: A00010 Br: F00010

And I need to split the data in table comment, so it become like this:

REG        INV      BR 
0001      B0001    F0001
0002      B0002    F0002
0003      B0003    F0003
0004      B0004    F0004
0005      B0005    F0005
00010      B00010    F00010

Any suggestions how to do this?

My Code .

;WITH Split_Names (Comment)
    AS
    -- Define the CTE query.
    (
        SELECT 
        CONVERT(XML,'<COMMENTS><Comment>'  
        + REPLACE(
     REPLACe(
     REPLACE(
     REPLACE(
    REPLACE(
     REPLACE(Comment,'Reg:',''),'Inv:',','),'Cus:',','),'Br:',','),' ' ,'')
        ,',', '</Comment><Comment>') + '</Comment></COMMENTS>') AS xmlComments
         FROM GenTransaction
    )
    -- Define the outer query referencing the CTE name.
     SELECT      
     Comment.value('/COMMENTS[1]/Comment[1]','varchar(100)') AS Reg,    
     Comment.value('/COMMENTS[1]/Comment[2]','varchar(100)') AS Inv,
     Comment.value('/COMMENTS[1]/Comment[3]','varchar(100)') AS Cus,
     Comment.value('/COMMENTS[1]/Comment[4]','varchar(100)') AS Br

     FROM Split_Names

My suggestion is to import your data into the columns you actually want. Inside SQL Server is not the best placr to be doing a cleanup like this. — Tim Biegeleisen, Aug 01 '17 at 08:28
@SQLAndOtherStuffGuy i tried, but because i get different length of data, so it not very useful for me — tang, Aug 01 '17 at 08:35
Is it always Reg, Inv, Cus and Br, or can there be other "columns" as well? — Zohar Peled, Aug 01 '17 at 08:41
@ZoharPeled yup , can be other "column" the important is nid to split the data — tang, Aug 01 '17 at 08:45
Read [Is storing a delimited list in a database column really that bad?](http://stackoverflow.com/questions/3653462/is-storing-a-delimited-list-in-a-database-column-really-that-bad), where you will see a lot of reasons why the answer to this question is **Absolutely yes!** — Zohar Peled, Aug 01 '17 at 08:49
And also, read Aaron Bertrand's [Split strings the right way – or the next best way](https://sqlperformance.com/2012/07/t-sql-queries/split-strings) — Zohar Peled, Aug 01 '17 at 08:51

Bogdan Sahlean · Accepted Answer · 2017-08-03T09:35:27.860

2

Following solution should work

[ 1 ] No matter which order is used for items of Comment column (Reg, Inv, Cus Br or Cus, Br, Reg, Inv or something else) and also

[ 2 ] Some items of Comment column could be missing (ex. TEST4 / Cus is missing)

[ 3 ] If Comment contains XML reserved chars then instead of REPLACE(t1.Comment, should be usedREPLACE((SELECT t1.Comment AS '*' FOR XML PATH('')),

DECLARE @Table1 TABLE (
    ID      INT NOT NULL,
    Name    NVARCHAR(50),
    Comment NVARCHAR(500)
);
INSERT  @Table1 (ID, Name, Comment)
VALUES 
(1, 'TEST ', 'Reg: 0001 Inv: B0001 Cus: A0001 Br: F0001'),
(2, 'TEST1', 'Reg: 0002 Inv: B0002 Cus: A0002 Br: F0002'),
(3, 'TEST2', 'Reg: 0003 Inv: B0003 Cus: A0003 Br: F0003'),
(4, 'TEST3', 'Reg: Coco&Jambo Inv: B0004 Cus: A0004 Br: F0004'), -- & = reserved XML char
(5, 'TEST4', 'Reg: 0005 Inv: B0005 Br: F0005'), -- Cus is missing
(5, 'TEST4', 'Cus: 3333 Br: 4444 Reg: 1111 Inv: 22222'); -- Different order

;WITH BaseQuery AS 
(
    SELECT  t1.ID, t1.Name, 
                CONVERT(XML, '<root><item>' 
                + REPLACE(
                    REPLACE(
                        REPLACE(
                            REPLACE((SELECT t1.Comment AS '*' FOR XML PATH('')), 
                            'Reg:', 
                            '</item><item type="Reg">'), 
                        'Inv:', 
                        '</item><item type="Inv">'), 
                    'Cus:', 
                    '</item><item type="Cus">'), 
                'Br:', 
                '</item><item type="Br">') + '</item></root>') CommentAsXml
    FROM    @Table1 t1
)
SELECT  bq.ID, bq.Name, 
        Reg = LTRIM(RTRIM(bq.CommentAsXml.value('(root/item[@type="Reg"])[1]', 'VARCHAR(11)'))),
        Inv = LTRIM(RTRIM(bq.CommentAsXml.value('(root/item[@type="Inv"])[1]', 'VARCHAR(11)'))),
        Cus = LTRIM(RTRIM(bq.CommentAsXml.value('(root/item[@type="Cus"])[1]', 'VARCHAR(11)'))),
        Br  = LTRIM(RTRIM(bq.CommentAsXml.value('(root/item[@type="Br"])[1]', 'VARCHAR(11)')))
FROM    BaseQuery bq

Results:

Demo here

edited Aug 03 '17 at 09:35

answered Aug 01 '17 at 09:06

Bogdan Sahlean

19,233
3
42
57

Msg 9411, Level 16, State 1, Line 1 XML parsing: line 1, character 45, semicolon expected Msg 9421, Level 16, State 1, Line 1 XML parsing: line 1, character 44, illegal name character – tang Aug 01 '17 at 09:19
line 1 which is from "------;WITH BaseQuery AS ----" – tang Aug 01 '17 at 09:20
@tang: copy & paste my example into new query window in SQL Server Management Studio. You have to get the same results as those from inserted picture. – Bogdan Sahlean Aug 01 '17 at 09:21
@tang: Have you succeed to copy & paste & run successfully this solution ? – Bogdan Sahlean Aug 01 '17 at 09:24
yes success, but i cant apply in my code, it gt error – tang Aug 01 '17 at 09:27
@tang: Check maximum length used for VARCHAR (11 in my solution) it it fits on your scenario. – Bogdan Sahlean Aug 02 '17 at 08:16
ok, so is it possible is use the data split from the column as id to join with other table variable? – tang Aug 02 '17 at 08:49
this is my another question, hope can get some suggestion https://stackoverflow.com/questions/45450874/how-to-join-cte-query-with-another-table-in-sql-server-2008 – tang Aug 02 '17 at 08:51
@tang: I'm going to take a look in few minutes. – Bogdan Sahlean Aug 02 '17 at 09:07

SQLAndOtherStuffGuy · Answer 2 · 2017-08-01T10:23:01.187

1

Try this. Basically what I am doing is replacing Reg:Inv: etc with commas, stripping out spaces and using XML to derive the columns values.

So I'm reffering to @Bogdan Sahlean solution below for handling XMl special characters in the comment field. Try this;

      ;WITH Split_Names (COMMENT)
        AS
        -- Define the CTE query.
        (
            SELECT 
            CONVERT(XML,'<COMMENTS><COMMENT>'  
    + 
    REPLACE(
    REPLACe(
    REPLACE(
    REPLACE(
    REPLACE(
    REPLACE(
    (SELECT Comment AS '*' FOR XML PATH('')) 
    ,'Reg:',''),'Inv:',','),'Cus:',','),'Br:',','),' ' ,'')
    ,',', '</COMMENT><COMMENT>') + '</COMMENT></COMMENTS>') AS xmlname
             FROM 


 GenTransaction
        )
        -- Define the outer query referencing the CTE name.
         SELECT      
         COMMENT.value('/COMMENTS[1]/COMMENT[1]','varchar(100)') AS Reg,    
         COMMENT.value('/COMMENTS[1]/COMMENT[2]','varchar(100)') AS Inv,
         COMMENT.value('/COMMENTS[1]/COMMENT[3]','varchar(100)') AS Cus,
         COMMENT.value('/COMMENTS[1]/COMMENT[4]','varchar(100)') AS Br

         FROM Split_Names

edited Aug 01 '17 at 10:23

answered Aug 01 '17 at 08:44

SQLAndOtherStuffGuy

214
1
5

sorry, i am not familiar in CTE what should i put in xmlname? – tang Aug 01 '17 at 08:56
Replace YOURTABLENAME with he name of the table you are working with. – SQLAndOtherStuffGuy Aug 01 '17 at 09:00
"----Msg 9421, Level 16, State 1, Line 1 XML parsing: line 1, character 44, illegal name character-----" i get this error – tang Aug 01 '17 at 09:10
Odd. I've run this SQL against your sample data and got the results in my screen shot. – SQLAndOtherStuffGuy Aug 01 '17 at 09:15
Actually my Column name is "Comment" i replace all the "COMMENT" word in your code, but it still get the same error – tang Aug 01 '17 at 09:35
I suspect you haven't copied the code correctly. The error you are getting would suggest that one of the XML Nodes has a name with an illegal character in it. Can you show a screen shot of your entire SQL? – SQLAndOtherStuffGuy Aug 01 '17 at 09:43
i jus copy your code, change the table field and run, the rest i dosen't edit anything. – tang Aug 01 '17 at 09:52
Ok then, I suspect some of the data in your comment fields has some XML type values or reserved characters, without seeing a more comprehensive set of values from the comment column would be difficult to see what's causing this. – SQLAndOtherStuffGuy Aug 01 '17 at 10:03
Looks good. Then I suspect as above that there is an issue with the data itself. – SQLAndOtherStuffGuy Aug 01 '17 at 10:06
is it i nid to include all my column name into CTE query? – tang Aug 01 '17 at 10:11
No, it's specifically related to the "Comment" Column as it's using xml on that column to derive values. Have a look at your data in Comment column. Is there anything that looks like it has characters like any of these. '&', '<', '>' – SQLAndOtherStuffGuy Aug 01 '17 at 10:15
Cool. I've updated the solution to handle this. Thanks to @Bogdan Sahlean – SQLAndOtherStuffGuy Aug 01 '17 at 10:24

how to split column when meet special character

2 Answers2