用于将字符串拆分为行和列的 SQL 查询

Question

我有一个以下格式的字符串:

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

我想在冒号(:)之后分割字符串，并在分号(;)之后开始一个新行。谁能帮我查询一下。

输出示例:

A B C

J K

P L J

Answer 1

试试这个 -

解决方案#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 != ''

输出:

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

解决方案#2:

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

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

输出:

A B C

J K

P L J

解决方案#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

输出:

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