问题描述
我有以下格式的字符串:
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