SQL Server 2000: remove duplicates from comma-sepa

问题:

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.

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

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

回答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.

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

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.