CODEWITHSUNDEEP
sqlsql-serverstringsql-server-2008t-sql
Ranju
Ranju

Reputation: 81

How to filter the String based on , and delete the duplicates

Let's Assume I have DataTable in Ms sql that represents simple matrix

Existing Table

    ids_cloumn
    ========================
    RX ,BX , AS , RX ,BX , AS
    XR ,Xs , AS     
    XR ,Xs , AS,XR ,Xs , AS
    RX ,BX , AS , 
    RX ,BX , AS ,

I want to filter the duplicated data in string and data is separated by ' , ' 

  ids_column
    ========================
    RX ,BX , AS 
    XR ,Xs , AS     
    XR ,Xs , AS
    RX ,BX , AS , 
    RX ,BX , AS ,

right now i am using this But this approach is unsuccessful 

declare @i int
declare @c char
declare @rst varchar(8000)
set @i=1
set @rst=substring(‘aaassrt’,1,1)
set @c=”

while @i<=len(‘aaassrt’)
begin
set @c=substring(‘aaassrt’,@i,1)
if charindex( @c,@rst,1)=0
set @rst=@rst+@c
set @i=@i+1
end
select @rst

Upvotes: 1

Views: 166

Answers (2)

wiretext
wiretext

Reputation: 3342

i hope this will help you xQuery but i am not able to remove last comma of string :( another thing for my below query you have to keep specific space between character 

declare @temp table (Id int,val nvarchar(33))
insert into @temp values (1,'RX ,BX ,AS ,RX ,BX ,AS')
insert into @temp values (2,'XR ,Xs ,AS     ')
insert into @temp values (3,'XR ,Xs ,AS ,XR ,Xs ,AS')
insert into @temp values (4,'RX ,BX ,AS ,')
insert into @temp values (5,'RX ,BX ,AS ,')

select Id,
REPLACE(cast(cast('<d>'+ replace(LTRIM(RTRIM(val)), ' ,','</d><d>')+'</d>'  as xml)
.query('distinct-values(/d)') as varchar), ' ', ' ,')AS [val]
from @temp;

RESULT

enter image description here

Upvotes: 2

Julien Vavasseur
Julien Vavasseur

Reputation: 3952

This is one way on doing it:

declare @data table(ids nvarchar(100))
insert into @data(ids) values
('1 , 2 , 4 , 23 , 1 , 2 , 4 , 23 ,')
, ('1 , 3 , 4 , 3 , 4 ')
, ('2 , 3 , 6 ,')
, ('3 , 5 , 8 , 3 , 5  ')
, ('1 , 7 , 9 ')

Select distinct xml.ids, LTRIM(RTRIM(x.id.value('.', 'varchar(5)'))) as id
From (
    Select ids
        , CAST('<x>'+REPLACE(ids, ',', '</x><x>') + '</x>' as xml) as data
    From @data
) as xml
Cross Apply data.nodes('x') as x(id)
Where LTRIM(RTRIM(x.id.value('.', 'varchar(5)'))) <> ''

Like it has been said by everyone else, you should not store comma separated value in one single column. You should record it in rows in another table and join it to your main table.

Upvotes: 0

Related Questions