Find unique values in a column of comma separated text

Question

I have a column "A" that are separated by commas and I want to find all the unique values in Column A.

Here's a very short example:

Column A
111, 222
333
444
777,999

I want a query which gives me the following value:

Column C
111
222
333
444
777
999

You should fix this design - it is not relational and is the reason you are having such an issue. — Oded, Dec 19 '11 at 19:28
This question is not clear enough. Do you mean "I want a query which splits the value in column A by commas, trims the resulting strings, and outputs one row per value"? — Chris Shain, Dec 19 '11 at 19:29
IMHO. Redo the data model. Storing comma separated lists in fields is a horrible model. It defeats the purpose of the relational database. — John Hartsock, Dec 19 '11 at 19:29
Possible duplicate of http://stackoverflow.com/questions/314824/t-sql-opposite-to-string-concatenation-how-to-split-string-into-multiple-reco — Chris Shain, Dec 19 '11 at 19:35
possible duplicate of [Irritative sql statement help needed](http://stackoverflow.com/questions/8564307/irritative-sql-statement-help-needed) — Mikael Eriksson, Dec 19 '11 at 19:39

score 2 · Answer 1 · edited May 23 '17 at 10:30

Ignoring the obvious problems with your table design as alluded to in all the comments and accepting that this might prove very slow on a huge table here's how I might do it.

First... I would create a statement that would turn all the rows into one big massive comma delimited list.

DECLARE @tmp VarChar(max)
SET @tmp = ''
SELECT @tmp = @tmp + ColumnA + ',' FROM TableA

Then use the table valued udf split described by this SO article to turn that massive string back into a table with a distinct clause to ensure that it's unique.

https://stackoverflow.com/a/2837662/261997

SELECT DISTINCT * FROM dbo.Split(',', @tmp)

score 0 · Answer 2 · edited May 23 '17 at 12:18

You can use the well-known Split function in combation with outer apply to split rows into multiple rows:

select  ltrim(rtrim(s.s)) as colC
from    @t t
cross apply
        dbo.split(',', t.colA) s

Full code example:

if object_id('dbo.Split') is not null
    drop function dbo.Split
go
CREATE FUNCTION dbo.Split (@sep char(1), @s varchar(512))
RETURNS table
AS
RETURN (
    WITH Pieces(pn, start, stop) AS (
      SELECT 1, 1, CHARINDEX(@sep, @s)
      UNION ALL
      SELECT pn + 1, stop + 1, CHARINDEX(@sep, @s, stop + 1)
      FROM Pieces
      WHERE stop > 0
    )
    SELECT pn,
      SUBSTRING(@s, start, CASE WHEN stop > 0 THEN stop-start ELSE 512 END) AS s
    FROM Pieces
  )
go
declare @t table (colA varchar(max))
insert @t select '111, 223'
union all select '333'
union all select '444'
union all select '777,999';

select  ltrim(rtrim(s.s)) as colC
from    @t t
cross apply
        dbo.split(',', t.colA) s

Find unique values in a column of comma separated text

2 Answers2

Linked