CODEWITHSUNDEEP
sql-server
Ashwanth K A
Ashwanth K A

Reputation: 131

how to remove duplicates from a comma seperated string in sql server

how to remove duplicate values from the comma seperated string in sql server. Without using functions

 Declare @data varchar(max) = '34.22,768.55,34.22,123.34,12,999.0,999.0'

My expected result should be

34.22,768.55,123.34,12,999.0

i tried this query but it doesn't remove duplicates from the variable.

Declare @data varchar(max) = '34.22,768.55,34.22,123.34,12,999.0,999.0'
set @data= (select '' + cast(cast('<d>'+replace(@data, ', ',',</d><d>')+'</d>'  as xml).query('distinct-values(/d)') as varchar(max)) +'')

Upvotes: 3

Views: 4970

Answers (4)

Shakeer Mirza
Shakeer Mirza

Reputation: 5110

Just an another simple way of doing it.

Declare @data Nvarchar(max) = N'34.22,768.55,34.22,123.34,12,999.0,999.0'
     , @data2 Nvarchar(max)='';
SELECT @data = N'SELECT @DATA_DIST= @DATA_DIST+VAL+'','' 
     FROM (SELECT '''+replace(@data,',',''' AS VAL UNION SELECT ''')+''')A';
EXECUTE sp_executesql @data,N'@DATA_DIST varchar(MAX) OUTPUT',@DATA_DIST=@data2 OUTPUT;
SELECT LEFT(@data2,LEN(@data2)-1);

Result:

12,123.34,34.22,768.55,999.0

Upvotes: 1

Pawan Kumar
Pawan Kumar

Reputation: 2021

Please try this -

DECLARE @x AS XML=''
Declare @finalstring varchar(max) = ''
DECLARE @Param AS VARCHAR(100) = '34.22,768.55,34.22,123.34,12,999.0,999.0'
SET @x = CAST('<A>'+ REPLACE(@Param,',','</A><A>')+ '</A>' AS XML)
select @finalstring = @finalstring + value + ',' from ( 
SELECT t.value('.', 'VARCHAR(10)') Value FROM @x.nodes('/A') AS x(t))p
GROUP BY value
PRINT SUBSTRING(@finalstring,0,LEN(@finalstring))

OUTPUT

12,123.34,34.22,768.55,999.0

For sql 2016+

Declare @data varchar(max) = '34.22,768.55,34.22,123.34,12,999.0,999.0'
Declare @finalstring varchar(max) = ''
select @finalstring = @finalstring + value + ',' from string_split(@data,',')
GROUP BY value
PRINT SUBSTRING(@finalstring,0,LEN(@finalstring))

OUTPUT

12,123.34,34.22,768.55,999.0

Upvotes: 2

Krishnraj Rana
Krishnraj Rana

Reputation: 6656

Try this

Declare @data varchar(max) = '34.22,768.55,34.22,123.34,12,999.0,999.0'

SELECT STUFF(
(
    SELECT DISTINCT ',' + UniqNum FROM
    (
        SELECT CAST('<d>'+replace(@data, ',','</d><d>')+'</d>' AS XML) AS numberXml
    ) as t1
    CROSS APPLY 
    (
     SELECT my_Data.D.value('.','varchar(50)') as UniqNum
     FROM t1.numberXml.nodes('d') as my_Data(D)
    ) t2
    FOR XML PATH('')
), 1, 1, '')

Result

UniqNumber
---------------------------
12,123.34,34.22,768.55,999.0

Upvotes: 2

Jayasurya Satheesh
Jayasurya Satheesh

Reputation: 8033

Try This

Declare @data varchar(max) = '34.22,768.55,34.22,123.34,12,999.0,999.0'

 ;WITH CTE
 AS
 (
    SELECT
        MyStr = SUBSTRING(@data,CHARINDEX(',',@Data)+1,LEN(@data)),
        Val = SUBSTRING(@data,1,CHARINDEX(',',@data)-1)

    UNION ALL

    SELECT
        MyStr = CASE WHEN CHARINDEX(',',MyStr)>0
                        THEN SUBSTRING(MyStr,CHARINDEX(',',MyStr)+1,LEN(MyStr))
                    ELSE NULL END,
        Val = CASE WHEN CHARINDEX(',',MyStr)>0
                        THEN SUBSTRING(MyStr,1,CHARINDEX(',',MyStr)-1)
                    ELSE MyStr END
        FROM CTE
            WHERE ISNULL(REPLACE(MyStr,',',''),'')<>''
 )
 SELECT
    Val = SUBSTRING(List,1,LEN(List)-1)
    FROM
     (
     SELECT
        DISTINCT Val+','
        FROM CTE
            WHERE ISNULL(MyStr ,'')<>''
            FOR XML PATH('')
    )Q(List)

My Result

12,123.34,34.22,768.55,999.0

Upvotes: 1

Related Questions