SQL Query for splitting the strings into rows and columns

Question

I have a string in following format:

A:B:C;J:K;P:L:J;

I want to split the string after colon(:) and start a new row after semicolon(;). Can anyone help me with a query.

Output Example:

A B C

J K

P L J

Answer 1

Try this one -

Solution #1:

DECLARE @t VARCHAR(100)
SELECT @t = 'A:B:C;J:K;P:L:J;'

SELECT * 
FROM (
     SELECT token = t.c.value('.', 'VARCHAR(100)')
     FROM
     (
          SELECT xmls = CAST('<t>' + 
               REPLACE(
                    REPLACE(@t, ':', ' '), 
                    ';', 
                    '</t><t>') + '</t>' AS XML)
     ) r
     CROSS APPLY xmls.nodes('/t') AS t(c)
) t
WHERE t.token != ''

Output:

----------
A B C
J K
P L J

Solution #2:

DECLARE @t VARCHAR(100)
SELECT @t = 'A:B:C;J:K;P:L:J;'

PRINT REPLACE(REPLACE(@t, ':', ' '), ';', CHAR(13) + CHAR(13))

Output:

A B C

J K

P L J

Solution #3:

DECLARE @t VARCHAR(100)
SELECT @t = 'A:B:C;J:K;P:L:J;'

SELECT [1], [2], [3]
FROM (
     SELECT 
            t2.id
          , t2.name
          , rn2 = ROW_NUMBER() OVER (PARTITION BY t2.id ORDER BY 1/0)  
     FROM (
          SELECT 
                id = t.c.value('@n', 'INT')
              , name = t.c.value('@s', 'CHAR(1)')
          FROM (
              SELECT x = CAST('<t s = "' + 
                    REPLACE(token + ':', ':', '" n = "' + CAST(rn AS VARCHAR(10)) 
                    + '" /><t s = "') + '" />' AS XML) 
               FROM (
                    SELECT 
                           token = t.c.value('.', 'VARCHAR(100)')
                         , rn = ROW_NUMBER() OVER (ORDER BY 1/0)
                    FROM (
                         SELECT x = CAST('<t>' + REPLACE(@t, ';', '</t><t>') + '</t>' AS XML)
                    ) r
                    CROSS APPLY x.nodes('/t') t(c)
               ) t
          ) d
          CROSS APPLY x.nodes('/t') t(c)
     ) t2
     WHERE t2.name != ''
) t3
PIVOT (
     MAX(name) FOR rn2 IN ([1], [2], [3])
) p

Output:

1    2    3
---- ---- ----
A    B    C
J    K    NULL
P    L    J