SQL Server 2000: remove duplicates from comma-separated string

Question

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?

Answer 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