1、将'1,1,2,3,4,4,4' 转换为'1,2,3,4'。
2、写法
DECLARE @Test varchar(100)='1,1,2,3,4,4,4'
SELECT
STUFF
(
(
SELECT DISTINCT ','+ y.sCode FROM
(
SELECT sCodes = CONVERT(xml,'<root><v>' + REPLACE(@Test, ',', '</v><v>') + '</v></root>')
) x
OUTER APPLY
(
SELECT sCode = N.v.value('.', 'varchar(100)') FROM x.sCodes.nodes('/root/v') N(v)
)y
FOR XML PATH('')
)
,1,1,''
) as sCodes