如何在 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)) +'')

- Ashwanth K A

---

你不想使用函数的原因是什么？ - Thom A

Answer 1

试试这个

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

SELECT STUFF(
(
    SELECT DISTINCT ',' + UniqNum FROM
    (
        SELECT CAST('<d>'+replace(@data, ',','</d><d>')+'</d>' AS XML) AS numberXml
    ) as t1
    CROSS APPLY 
    (
     SELECT my_Data.D.value('.','varchar(50)') as UniqNum
     FROM t1.numberXml.nodes('d') as my_Data(D)
    ) t2
    FOR XML PATH('')
), 1, 1, '')

结果

UniqNumber
---------------------------
12,123.34,34.22,768.55,999.0

- Krishnraj Rana

Answer 2

请尝试这个方法 -

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))

OUTPUT

12,123.34,34.22,768.55,999.0

- Pawan Kumar

---

问题是，OP说“不使用函数”。你在这里使用了STRING_SPLIT函数。然而，我仍然想知道为什么OP不想使用它。 - Thom A

是的，你说得对，不知道为什么不使用函数来做这件事。STRING_SPLIT 是拆分字符串的最佳方法。 - Pawan Kumar

我喜欢这个2016年以后的答案。 - Bill

这个能在2016及以上版本中作为计算列吗？ - Bill

Answer 3

试一下这个

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

 ;WITH CTE
 AS
 (
    SELECT
        MyStr = SUBSTRING(@data,CHARINDEX(',',@Data)+1,LEN(@data)),
        Val = SUBSTRING(@data,1,CHARINDEX(',',@data)-1)

    UNION ALL

    SELECT
        MyStr = CASE WHEN CHARINDEX(',',MyStr)>0
                        THEN SUBSTRING(MyStr,CHARINDEX(',',MyStr)+1,LEN(MyStr))
                    ELSE NULL END,
        Val = CASE WHEN CHARINDEX(',',MyStr)>0
                        THEN SUBSTRING(MyStr,1,CHARINDEX(',',MyStr)-1)
                    ELSE MyStr END
        FROM CTE
            WHERE ISNULL(REPLACE(MyStr,',',''),'')<>''
 )
 SELECT
    Val = SUBSTRING(List,1,LEN(List)-1)
    FROM
     (
     SELECT
        DISTINCT Val+','
        FROM CTE
            WHERE ISNULL(MyStr ,'')<>''
            FOR XML PATH('')
    )Q(List)

我的结果

12,123.34,34.22,768.55,999.0

- Jayasurya Satheesh

Answer 4

这只是另一种简单的实现方式。

Declare @data Nvarchar(max) = N'34.22,768.55,34.22,123.34,12,999.0,999.0'
     , @data2 Nvarchar(max)='';
SELECT @data = N'SELECT @DATA_DIST= @DATA_DIST+VAL+'','' 
     FROM (SELECT '''+replace(@data,',',''' AS VAL UNION SELECT ''')+''')A';
EXECUTE sp_executesql @data,N'@DATA_DIST varchar(MAX) OUTPUT',@DATA_DIST=@data2 OUTPUT;
SELECT LEFT(@data2,LEN(@data2)-1);

Result:

12,123.34,34.22,768.55,999.0

- Shakeer Mirza