SQL Server：根据值将字符串中的逗号分隔值转换为存储桶

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

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