SQL Server 2000：从逗号分隔的字符串中删除重复项

I have been looking into this for a while now and I cannot find a way to remove duplicate strings from a comma-separated string in SQL Server 2000. I can find a lot of examples of this for SQL Server 2005 and 2008 but not 2000.

我已经研究了一段时间了,我找不到从SQL Server 2000中的逗号分隔字符串中删除重复字符串的方法。我可以找到很多SQL Server 2005和2008但不是2000的例子。

Given the string test,test2,test,test3,test2

给出字符串测试,test2,test,test3,test2

does anyone know how would you return test,test2,test3?

有谁知道你会如何返回test,test2,test3?

3 个解决方案

#1

You can use while loop to parse the string and put the values you find in a temporary variable and before you add the value you do a check if it is already added.

您可以使用while循环来解析字符串并将找到的值放在临时变量中,在添加值之前,请检查是否已添加。

declare @S varchar(50)
declare @T varchar(50)
declare @W varchar(50)

set @S = 'test,test2,test,test3,test2'
set @T = ','

while len(@S) > 0
begin
  set @W = left(@S, charindex(',', @S+',')-1)+','
  if charindex(','+@W, @T) = 0
    set @T = @T + @W
  set @S = stuff(@S, 1, charindex(',', @S+','), '')
end

set @S = substring(@T, 2, len(@T)-2)

print @S

If you want to do this in a query you need to put the code above in a function.

如果要在查询中执行此操作,则需要将上面的代码放在函数中。

create function dbo.RemoveDups(@S varchar(50))
returns varchar(50)
as
begin
  declare @T varchar(50)
  declare @W varchar(50)

  set @T = ','

  while len(@S) > 0
  begin
    set @W = left(@S, charindex(',', @S+',')-1)+','
    if charindex(','+@W, @T) = 0
      set @T = @T + @W
    set @S = stuff(@S, 1, charindex(',', @S+','), '')
  end

  return substring(@T, 2, len(@T)-2)
end

And use it like this

并像这样使用它

select dbo.RemoveDups(ColumnName) as DupeFreeString
from YourTable

#2

I was looking for this in Oracle. And i stumbled upon another solution. For those who are trying to do the same with a query in Oracle. Try the below query

我在Oracle中寻找这个。我偶然发现了另一种解决方案。对于那些试图在Oracle中使用查询执行相同操作的人。尝试以下查询

with t as (select 'SCOTT,ALLEN,KING,SCOTT' as in_cls from dual)
, t1 as ( select distinct regexp_substr(in_cls, '[^,]+', 1, rownum) names
                  from t
                  connect by rownum <= length(regexp_replace(in_cls, '[^,]'))+1)
SELECT 
    RTrim(xmlagg(xmlelement(a,names||',').extract('//text()')),',') string
from t1

Result STRING

ALLEN,SCOTT,KING

#3

Might need a little tweaking. I widened the fields a bit and then tried:

可能需要一点调整。我稍稍扩大了字段然后尝试:

SELECT dbo.RemoveDups('Procedure, Missing Attestation, Procedure, Incomplete Note, Missing technique');

But it missed the dup on Procedure.

但它错过了程序上的重复。

#1