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, Nov 04 '15 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, Nov 04 '15 at 13:19
@Ranju This is irony. The reality is that data is column should be **atomic** — Lukasz Szozda, Nov 04 '15 at 13:26
You refer this link http://stackoverflow.com/questions/5493510/turning-a-comma-separated-string-into-individual-rows may be resolved staff — Mukesh Kalgude, Nov 04 '15 at 13:31
@Ranju The way you have data in a column (1,2,24,...) is delimited strings because data is in the form of a string delimited(separated) by a comma. Gordon is saying (correctly) that it is a very bad data structure and is hard to use. He is also explaining how to represent a matrix in SQL. — Adish, Nov 04 '15 at 13:34

wiretext · Accepted Answer · 2015-11-04T14:04:30.963

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 04 '15 at 14:04

answered Nov 04 '15 at 13:44

wiretext

3,302
14
19

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 Nov 04 '15 at 13:59
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 – Ranju Nov 04 '15 at 14:49
instead of `varchar` define `nvarchar(max)` – wiretext Nov 04 '15 at 14:57
Can you explain this what happening here REPLACE(cast(cast(''+ replace(LTRIM(RTRIM(val)), ' ,','')+'' as xml) .query('distinct-values(/d)') as varchar), ' ', ' ,')AS [val] – Ranju Nov 04 '15 at 15:53
@Ranju convert string into XML then apply query on XML then replace comma on result – wiretext Nov 04 '15 at 17:33

score 0 · Answer 2 · answered Nov 04 '15 at 13:37

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.

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

2 Answers2