sql convert single column to multiple based on delimiter

Question

I would like to "replicate" the excel(data) text to columns function. basically I have a string column, and I would like to split it to multiple columns based on a specific delimiter.

 declare @t table (
    id int,
    name varchar(50)
    )

    insert into @t values (1,'abcd.ef.g')
    insert into @t values (2,'ab.cdef.ghi')
    insert into @t values (3,'a..d')
    insert into @t values (4,'a')

    select * from @t

    id          name
    ----------- --------------------------------------------------
    1           abcd.ef.g
    2           ab.cdef.ghi
    3           a..d
    4           a

    --I would like to get result

    id          name1   name2   name3
    ----------- --------------------------------------------------
    1           abcd    ef      g
    2           ab      cdef    ghi
    3           a       (NULL)  d
    4           a       (NULL)  (NULL)

thanks

possible duplicate of [How to split a comma-separated value to columns](http://stackoverflow.com/questions/10581772/how-to-split-a-comma-separated-value-to-columns) — Emilio Gort, Sep 03 '15 at 15:43
A splitter would be total overkill here. Just use PARSENAME to get the separate elements. — Sean Lange, Sep 03 '15 at 15:47
Actually with your data parsename probably won't work very well but some basic substring logic should do this easily. If possible you should permanently separate this data. Storing delimited data violates 1NF — Sean Lange, Sep 03 '15 at 15:49

score 1 · Accepted Answer · answered Sep 03 '15 at 15:59

 declare @t table (id int,name varchar(50))

    insert into @t values (1,'abcd.ef.g')
    insert into @t values (2,'ab.cdef.ghi')
    insert into @t values (3,'a..d')
    insert into @t values (4,'a');



WITH Split_Fields (ID, xmlfields)
AS
(
    SELECT ID ,
    CONVERT(XML,'<Fields><field>'  
    + REPLACE(name,'.', '</field><field>') + '</field></Fields>') AS xmlfields
      FROM @t
)

 SELECT ID      
 ,xmlfields.value('/Fields[1]/field[1]','varchar(100)') AS Field1    
 ,xmlfields.value('/Fields[1]/field[2]','varchar(100)') AS Field2
 ,xmlfields.value('/Fields[1]/field[3]','varchar(100)') AS Field3    
 ,xmlfields.value('/Fields[1]/field[4]','varchar(100)') AS Field4
 ,xmlfields.value('/Fields[1]/field[5]','varchar(100)') AS Field5
 FROM Split_Fields

Result

╔════╦════════╦════════╦════════╦════════╦════════╗
║ ID ║ Field1 ║ Field2 ║ Field3 ║ Field4 ║ Field5 ║
╠════╬════════╬════════╬════════╬════════╬════════╣
║  1 ║ abcd   ║ ef     ║ g      ║ NULL   ║ NULL   ║
║  2 ║ ab     ║ cdef   ║ ghi    ║ NULL   ║ NULL   ║
║  3 ║ a      ║        ║ d      ║ NULL   ║ NULL   ║
║  4 ║ a      ║ NULL   ║ NULL   ║ NULL   ║ NULL   ║
╚════╩════════╩════════╩════════╩════════╩════════╝

sql convert single column to multiple based on delimiter

1 Answers1

Result