Have a string "A,B,H1,J,P1,H3,L2" in a column. Need to segregate this into rows based on the number appended/not appended with each. So for the above example I should have 4 rows as output:
在一列中有一个字符串“A,B,H1,J,P1,H3,L2”。需要根据附加/不附加的数字将其分隔成行。因此,对于上面的示例,我应该有4行作为输出:
ABJ (no numbers associated with it)
HP (1 appended to it)
L (2 appended to it)
H (3 appended to it)
1 个解决方案
#1
0
With the help of xml nodes:
在xml节点的帮助下:
DECLARE @string nvarchar(max) = 'A,B,H1,J,P1,H3,L2',
@xml xml
SELECT @xml = CAST('<a>' + REPLACE(@string,',','</a><a>') +'</a>' as xml)
;WITH cte AS (
SELECT t.v.value('.','nvarchar(10)') as v,
CASE WHEN TRY_CAST(RIGHT(t.v.value('.','nvarchar(10)'),1) as int) IS NULL THEN 0
ELSE CAST(RIGHT(t.v.value('.','nvarchar(10)'),1) as int) END as g
FROM @xml.nodes('/a') as t(v)
)
SELECT REPLACE((
SELECT v +''
FROM cte c1
WHERE c1.g= c.g
FOR XML PATH ('')
),g,'') as [values]
FROM cte c
GROUP BY g
ORDER BY g
Output:
输出:
values
------
ABJ
HP
L
H
#1
0
With the help of xml nodes:
在xml节点的帮助下:
DECLARE @string nvarchar(max) = 'A,B,H1,J,P1,H3,L2',
@xml xml
SELECT @xml = CAST('<a>' + REPLACE(@string,',','</a><a>') +'</a>' as xml)
;WITH cte AS (
SELECT t.v.value('.','nvarchar(10)') as v,
CASE WHEN TRY_CAST(RIGHT(t.v.value('.','nvarchar(10)'),1) as int) IS NULL THEN 0
ELSE CAST(RIGHT(t.v.value('.','nvarchar(10)'),1) as int) END as g
FROM @xml.nodes('/a') as t(v)
)
SELECT REPLACE((
SELECT v +''
FROM cte c1
WHERE c1.g= c.g
FOR XML PATH ('')
),g,'') as [values]
FROM cte c
GROUP BY g
ORDER BY g
Output:
输出:
values
------
ABJ
HP
L
H