sql-server - 如何从sql server中的逗号分隔字符串中删除重复项

Question

如何从 sql server 中的逗号分隔字符串中删除重复值。不使用函数

 Declare @data varchar(max) = '34.22,768.55,34.22,123.34,12,999.0,999.0'

我的预期结果应该是

34.22,768.55,123.34,12,999.0

我试过这个查询，但它没有从变量中删除重复项。

Declare @data varchar(max) = '34.22,768.55,34.22,123.34,12,999.0,999.0'
set @data= (select '' + cast(cast('<d>'+replace(@data, ', ',',</d><d>')+'</d>'  as xml).query('distinct-values(/d)') as varchar(max)) +'')

Answer 1

请试试这个 -

DECLARE @x AS XML=''
Declare @finalstring varchar(max) = ''
DECLARE @Param AS VARCHAR(100) = '34.22,768.55,34.22,123.34,12,999.0,999.0'
SET @x = CAST('<A>'+ REPLACE(@Param,',','</A><A>')+ '</A>' AS XML)
select @finalstring = @finalstring + value + ',' from ( 
SELECT t.value('.', 'VARCHAR(10)') Value FROM @x.nodes('/A') AS x(t))p
GROUP BY value
PRINT SUBSTRING(@finalstring,0,LEN(@finalstring))

输出

12,123.34,34.22,768.55,999.0

对于 sql 2016+

Declare @data varchar(max) = '34.22,768.55,34.22,123.34,12,999.0,999.0'
Declare @finalstring varchar(max) = ''
select @finalstring = @finalstring + value + ',' from string_split(@data,',')
GROUP BY value
PRINT SUBSTRING(@finalstring,0,LEN(@finalstring))

输出

12,123.34,34.22,768.55,999.0