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

问题描述

我有以下格式的字符串:

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.

输出示例:

A B C

J K

P L J

推荐答案

不确定，我理解正确，但是如果您需要将数据作为三列行集:

Not sure, I understand correctly, but if you need data as three columns rowset:

declare @str nvarchar(max)
set @str = 'A:B:C;J:K;P:L:J;'

select p.[1] as Column1, p.[2] as Column2, p.[3] as Column3
from (
    select T.c.value('.', 'nvarchar(200)') [row], row_number() over (order by @@spid) rn1
    from (select cast('<r>' + replace(@str, ';', '</r><r>') + '</r>' as xml) xmlRows) [rows]
        cross apply xmlRows.nodes('/r') as T(c)
    where T.c.value('.', 'nvarchar(200)') != ''
    ) t1
    cross apply (
         select NullIf(T.c.value('.', 'nvarchar(200)'), '') row2,
            row_number() over (order by @@spid) rn
         from (select cast('<r>' + replace(t1.row, ':', '</r><r>') + '</r>' as xml) xmlRows) [rows]
            cross apply xmlRows.nodes('/r') as T(c)
    ) t2
    pivot (max(t2.row2) for t2.rn in ([1], [2], [3])) p
order by p.rn1

输出

Column1  Column2  Column3
-------- -------- -------
A        B        C
J        K        NULL
P        L        J