Getting values of comma separated fields in SQL Server

Question

I have comma separated column which represents the ids of cities like:

ID | Name
1  | 1,2,3 
2  | 2,3,4

I want to make query to get name of the this value field. There is City Table which has two columns: id and name of cities

EXPECTED OUTPUT

ID | VALUES    
1  | mumbai,delhi,pune
2  | delhi,pune,chennai

I can make a query if there is only one id in a column like:

select data.id,city.name from data,city where data.values=city.cityid

but I am not getting how to retrieve the data if there are multiple comma-separated values in one field.

The database design is bad, you should consider learning about *normalization*. — potashin, Mar 06 '15 at 20:58
That one might be tricky to do without some funky querying. I'd probably pull the data back to your app, and let it sort everything out and re-query the cities table as needed, then have the app produce the output you want. — Joe Enos, Mar 06 '15 at 20:58
you should use the data in the first table to build a new table (one time) that can then use standard joins to get your results. — Hogan, Mar 06 '15 at 21:01
It's possible, but it is a pain, because there is not very much built in to support it. You are not supposed to design a database with comma separated values. — Guffa, Mar 06 '15 at 21:01
hmm that i can do i can handle it by firing two queries one by one .. and handle it on my app side .. but was thinking if there is some way to do it in one query then it will be more good . :( :( — user4410615, Mar 06 '15 at 21:03
if any one can give a small example . how can i do it .. i will thnkful — user4410615, Mar 06 '15 at 21:04
i know database design is not good but .. already it is live app and i can't change database now . — user4410615, Mar 06 '15 at 21:06
Are you using MySQL or Microsoft SQL Server? You have tagged the question with both... — Guffa, Mar 06 '15 at 21:10
I see lots of people telling how horrible the design is. While i agree with that you still need a way to deal with this. You need a string splitter. Check out this article. http://sqlperformance.com/2012/07/t-sql-queries/split-strings — Sean Lange, Mar 06 '15 at 21:20

score 1 · Answer 1 · edited May 23 '17 at 12:20

to do this please do following sections:
1-Create Function to get table of comma separate value in each row

CREATE FUNCTION [dbo].[fn_Split](
@ForigenKey INT,
 @String NVARCHAR (4000),
 @Delimiter NVARCHAR(10)
 )
RETURNS @ValueTable TABLE ([ID] INT IDENTITY NOT NULL,FID int null,[Value] NVARCHAR(4000))
BEGIN
 DECLARE @NextString NVARCHAR(4000)
 DECLARE @Pos INT
 DECLARE @NextPos INT
 DECLARE @CommaCheck NVARCHAR(1)

 --Initialize
 SET @NextString = ''
 SET @CommaCheck = RIGHT(@String,1) 

 --Check for trailing Comma, if not exists, INSERT
 --if (@CommaCheck <> @Delimiter )
 SET @String = @String + @Delimiter

 --Get position of first Comma
 SET @Pos = CHARINDEX(@Delimiter,@String)
 SET @NextPos = LEN(@Delimiter)

 --Loop while there is still a comma in the String of levels
 WHILE (@pos <>  0)  
 BEGIN
  SET @NextString = SUBSTRING(@String, 1, @Pos - 1)

  INSERT INTO @ValueTable ( FID,[Value]) VALUES (@ForigenKey ,@NextString)

  SET @String = SUBSTRING(@String,@pos + LEN(@Delimiter),LEN(@String))

  SET @NextPos = @Pos
  SET @pos  = CHARINDEX(@Delimiter,@String)
 END

 RETURN
END

GO

2- create Concat Aggregate with the folwing link
Concat Aggregate

3- you can get your data with below select

DECLARE @ID INT,@Name NVARCHAR(4000)
 DECLARE @ValueTable table ([ID] int NOT NULL,[Value] INT)
DECLARE mycur CURSOR FOR
SELECT TOP(1000) ID,Name FROM TableA
OPEN mycur
FETCH NEXT FROM mycur INTO @ID,@Name
WHILE(@@FETCH_STATUS=0)
BEGIN
    INSERT INTO @ValueTable
            ( ID, Value )
    SELECT @ID,Value FROM dbo.fn_Split(@Name,',')
    FETCH NEXT FROM mycur INTO @ID,@Name
END
CLOSE mycur
DEALLOCATE mycur
SELECT * FROM @ValueTable


SELECT ID,dbo.ConcatAggregate(CityName) FROM @ValueTable
inner join city on value=cityid GROUP BY ID

Of all the possible ways of splitting strings in sql the while nibbler version is by far the worst for performance. See this article for a myriad of better options. http://sqlperformance.com/2012/07/t-sql-queries/split-strings — Sean Lange, Mar 06 '15 at 21:21
@user4410615 of curse you can do it with out clr function, but clr function is better — Mehdi Haghshenas, Mar 06 '15 at 21:29

score 1 · Accepted Answer · edited Jan 18 '21 at 12:30

The easy way is to convert CSV values to rows for each Id, join that with CITY table and convert back to CSV values. I have written the logic inside the query.

;WITH CTE1 AS
(
    -- Convert CSV to rows
    SELECT Id,LTRIM(RTRIM(Split.a.value('.', 'VARCHAR(100)'))) 'NAME' 
    FROM  
    (
         -- To change ',' to any other delimeter, just change ',' before '</M><M>' to your desired one
         SELECT Id,CAST ('<M>' + REPLACE(Name, ',', '</M><M>') + '</M>' AS XML) AS Data 
         FROM #TEMP     
    ) AS A 
    CROSS APPLY Data.nodes ('/M') AS Split(a)
)
,CTE2 AS
(
    -- Now join the values in rows with Id in CITY table
    SELECT T.ID,T.NAME,C.CITYNAME
    FROM CTE1 T
    JOIN #CITY C ON T.NAME=C.ID
)
-- Now convert back to CSV format
SELECT DISTINCT ID,
 SUBSTRING(
            (SELECT ', ' + CITYNAME
            FROM CTE2 I  
            WHERE I.Id=O.Id             
            FOR XML PATH('')),2,200000) [VALUES]
FROM CTE2 O

Click here to view result

Getting values of comma separated fields in SQL Server

2 Answers2