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. — souslov
– souslov, Commented Mar 6, 2015 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
– Joe Enos, Commented Mar 6, 2015 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
– Hogan, Commented Mar 6, 2015 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
– Guffa, Commented Mar 6, 2015 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
– user4410615, Commented Mar 6, 2015 at 21:03

Community · Accepted Answer · 2021-01-18 12:30:34Z

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

Community · Accepted Answer · 2017-05-23 12:20:44Z

1

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

edited May 23, 2017 at 12:20

CommunityBot

11 silver badge

answered Mar 6, 2015 at 21:02

Mehdi Haghshenas

2,4632 gold badges19 silver badges38 bronze badges

3 Comments

user4410615 Over a year ago

and sir how can i use this function ?

Sean Lange Over a year ago

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. sqlperformance.com/2012/07/t-sql-queries/split-strings

Mehdi Haghshenas Over a year ago

@user4410615 of curse you can do it with out clr function, but clr function is better

Collectives™ on Stack Overflow

Getting values of comma separated fields in SQL Server

2 Answers 2

Comments

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related