how to find duplicate values in sql server which separated by comma in jumbled order

Question

I have a table like this in SQL Server 2012 and one of the column values which is separated by comma

field 1  field 2
1,2,3     a
4,5,6     b
3,2,1     c
2,3,1     d
6,5,4     e
7,5,9     f

How to write a query for getting result like

field 1   field 2
1,2,3      a
3,2,1      c
2,3,1      d
4,5,6      b
6,5,4      e
7,5,9      f

can any one help please how to get this

Hi GordonLinoff, thanks for your response. I want to display the result like this so is there any other way for getting the above result. For understanding purpose i was given numbers in field 1, basically in my table i am having string. — chakri, Feb 23 '16 at 21:57
Please read this post and consider redesigning your schema: http://stackoverflow.com/questions/3653462/is-storing-a-delimited-list-in-a-database-column-really-that-bad — rghome, Feb 23 '16 at 21:57
I think I know what the user wants.... a, c, and d are duplicates; b and e are duplicates... Is this correct @chakri? — user2065377, Feb 23 '16 at 22:10
Hi rghome, if i change my schema like displaying field1 values like 1 2 3 in different rows with same id and again those are repeated like 2 3 1 with another id or 3 2 1 like that.. Then it is possible to find it easily. — chakri, Feb 23 '16 at 22:12
Can the numbers each have more than 1 digit, i.e. `> 9` ? Also: are these numbers references to some IDs in another table? — trincot, Feb 23 '16 at 22:19
yes trincot these numbers are more than 1 digit and lessthan 9. But these numbers not using any other table references. — chakri, Feb 23 '16 at 22:22
Just to be sure, this could be a value: `123,67,81,2505,42,166`? — trincot, Feb 23 '16 at 22:27
numbers are anything. But the no of numbers which is separated by comma in one row is less than 9 — chakri, Feb 23 '16 at 22:40
Would it be an acceptable solution if it required you to create a table with all possible numbers in it (just one per record; no commas)? — trincot, Feb 23 '16 at 22:42
@trincot, instead of string i given numbers for understanding purpose. Basically in my table string is separating with commas like abcd,ef,gh and again it is repeated like ef,abcd,gh or gh,ef,abcd.. so user wants to know. — chakri, Feb 23 '16 at 22:54
Ok, well this is not something that database engines are designed for. Do you have a programming language environment where you connect to the database? You probably can do these kinds of comparisons better in there. — trincot, Feb 23 '16 at 22:58
@trincot, so what's your suggestion for completing this task. Is there any changes required in my table to pull that type of data. — chakri, Feb 23 '16 at 23:04
Well, lets say you are running PHP, you could read the raw table data, and filter out the duplicates. In PHP it is rather easy to rearrange those comma-separated values and detect duplicates. Same will be for other environments, like node.js, Java, VB.net, ColdFusion, ... etc, you just have to tell which environment you use, and whether you would accept that as a solution. — trincot, Feb 23 '16 at 23:06
If i change my table structure like splitting that string which is separated by comma into individual rows with same id, then is it possible to find those repeated values — chakri, Feb 23 '16 at 23:12
@trincot, if you don't mind give me your email id i will send you clearly — chakri, Feb 23 '16 at 23:16
Sorting with individual rows, that is what SQL is good at, but that first part will require stored procedure, and a lot of difficult to read code, which can be done sooooo much easier in other languages. You don't use a programming environment? — trincot, Feb 23 '16 at 23:17
@chakri, I meant: do you use a programming environment, and if so: which. — trincot, Feb 24 '16 at 15:14
@trincot, i am trying to change my table structure i.e. splitting those comma separated string value into individual rows with same id.. Then it is possible to write query in sql — chakri, Feb 24 '16 at 15:30

score 1 · Answer 1 · edited May 23 '17 at 11:59

first of all, this is an extremely funky problem, hence the solution is also totally berserk, so don't be critical and just have fun...

first you would need to create this split function:

CREATE function [dbo].[f_split](@param nvarchar(max), @delimiter char(1))
returns @t table (val nvarchar(max), seq int) as
begin

set @param += @delimiter

;with a as
(
select cast(1 as bigint) f, charindex(@delimiter, @param) t, 1 seq
union all
select t + 1, charindex(@delimiter, @param, t + 1), seq + 1
from a
where charindex(@delimiter, @param, t + 1) > 0
)
insert @t
select substring(@param, f, t - f), seq from a
option (maxrecursion 0)
return
end

courtesy of Parse comma-separated string to make IN List of strings in the Where clause

Second, split and order into temporary table:

create table #a ([field 1] varchar (20), [field 2] varchar(20))
insert #a ([field 1], [field 2])
select '1,2,3','a' union all
select '4,5,6','b' union all
select '3,2,1','c' union all
select '2,3,1','d' union all
select '6,5,4','e' union all
select '7,5,9','f'

select a.[field 2],b.val
into #tt
    from #a a
        cross apply [dbo].[f_split] ([field 1],',') b
    order by 1,2

Thirdly, slice and dice it whichever way you want:

SELECT [field 2],theCommaDelimitedVal, ROW_NUMBER () over ( partition by theCommaDelimitedVal order by [field 2])
FROM (
        select e.[field 2],
        stuff(
                (select ','+cast(i.val as varchar(1)) as [text()]
                from #tt i
                where i.[field 2] = e.[field 2]
                for xml path (''))
            ,1,1,'') as theCommaDelimitedVal 
        from #tt e
        group by e.[field 2]
    ) a
group by [field 2],theCommaDelimitedVal

and I certainly hope nobody thinks me crazy for taking all of this serious :)... at least it was rather extraordinary

how to find duplicate values in sql server which separated by comma in jumbled order

1 Answers1