SQL Server 2000:从逗号分隔的字符串中删除重复项 [英] SQL Server 2000: remove duplicates from comma-separated string

问题描述

我已经研究了一段时间，但我找不到从 SQL Server 2000 中的逗号分隔字符串中删除重复字符串的方法.我可以找到很多关于 SQL Server 2005 和 2008 的示例，但是不是 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.

给定字符串测试，测试2，测试，测试3，测试2

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

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

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

推荐答案

您可以使用 while 循环来解析字符串并将您找到的值放入一个临时变量中，然后在添加该值之前检查它是否已经存在已添加.

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.

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

然后像这样使用

select dbo.RemoveDups(ColumnName) as DupeFreeString
from YourTable

这篇关于SQL Server 2000:从逗号分隔的字符串中删除重复项的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！