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?

Counterpane

DB have to be normalized. – Disregardful

If you really can't do this in the client, create a UDF to split the data (sommarskog.se/arrays-in-sql-2000.html#tblnum) then simply select distinct from it – Indohittite

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

Prajna

yes, this works perfectly... thanks for the help as i could not find anything else like this online :) – Counterpane

Answer 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

Virtual

Answer 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.

Armistead