CODEWITHSUNDEEP
sql-serversql-server-2008split
tang
tang

Reputation: 61

how to split column when meet special character

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

Upvotes: 1

Views: 453

Answers (2)

Bogdan Sahlean
Bogdan Sahlean

Reputation: 1

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:

enter image description here

Demo here

Upvotes: 2

SQLAndOtherStuffGuy
SQLAndOtherStuffGuy

Reputation: 214

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.

enter image description here

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

Upvotes: 1

Related Questions