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

Question

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

Comma separated string in column +1. This solution provides extraordinary performance, it is easy to extend and parse :) — Lukasz Szozda
– Lukasz Szozda, Commented Nov 4, 2015 at 13:19
The proper way to represent a matrix in SQL is using a table with (essentially) three columns for row, column, and value. Using delimited lists is simply a very bad data structure that is hard to use. — Gordon Linoff
– Gordon Linoff, Commented Nov 4, 2015 at 13:19
@Ranju This is irony. The reality is that data is column should be atomic — Lukasz Szozda
– Lukasz Szozda, Commented Nov 4, 2015 at 13:26

wiretext · Accepted Answer · 2015-11-04 14:04:30Z

2

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

edited Nov 4, 2015 at 14:04

answered Nov 4, 2015 at 13:44

wiretext

3,35216 silver badges19 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Ranju Over a year ago

I am happy for the solution but please help me the above dataTable its not working that :( i am not able apply for the Table in SQL

Ranju Over a year ago

may be you can help me on this you know how to solve this Msg 6354, Level 16, State 10, Line 2 Target string size is too small to represent the XML instance

wiretext Over a year ago

instead of varchar define nvarchar(max)

Ranju Over a year ago

Can you explain this what happening here REPLACE(cast(cast('<d>'+ replace(LTRIM(RTRIM(val)), ' ,','</d><d>')+'</d>' as xml) .query('distinct-values(/d)') as varchar), ' ', ' ,')AS [val]

wiretext Over a year ago

@Ranju convert string into XML then apply query on XML then replace comma on result

Julien Vavasseur · Accepted Answer · 2015-11-04 13:37:00Z

0

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.

answered Nov 4, 2015 at 13:37

Julien Vavasseur

3,9701 gold badge22 silver badges30 bronze badges

Collectives™ on Stack Overflow

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

2 Answers 2

5 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

5 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related