Replace comma separated values in SQL Server

Question

I have two SQL Tables EmployeeMst and EmployeeCity

EmployeeMst
SrNo  Name 
1     abc
2     xyz
3     pqr
4     def

EmployeeCity
srno  City     EmplSrNo
1     Delhi    1,2,3,4
2     Mumbai   2,3,1
3     New York 3,2

i want to get Employee Name from EmployeeMst with select query

OUTPUT LIKE BELOW:

srno City          EmployeeName
1    Delhi         abc,xyz,pqr,def
2    Mumbai        xyz,pqr,abc
3    New York      pqr,xyz

Therea are several data in this table. Please give me query how can i do this

i used charindex but it takes more time.

This is pretty bad database design, are you able to change it? — DavidG, Dec 02 '14 at 11:56
Fix the database design by using a junction table instead of trying to make this work. — Gordon Linoff, Dec 02 '14 at 11:56
yes, i can change but this is old table so i have to use only this table — Hiren gardhariya, Dec 02 '14 at 11:57
I have a similar problem with the following caveats: **The entire solution must be runnable as a subquery**. That means no temp tables, no functions, no Declares and I can't redesign the database. — mnemotronic, Nov 04 '16 at 22:43

score 6 · Accepted Answer · edited May 23 '17 at 12:34

I still think that regardless of whether or not the table is old or new, you should take the time to fix poor design sooner rather than later. You are only delaying the inevitable. As such here is something to get you started on a normalised design:

-- CREATE CITY TABLE
CREATE TABLE dbo.City
(
    SrNo INT,
    City VARCHAR(50)
);

-- POPULATE FROM EXISTING TABLE
INSERT dbo.City (SrNo, City)
SELECT  SrNo, City
FROM    dbo.EmployeeCity;

-- CREATE CITY-EMPLOYEE JUNCTION TABLE USING EXISTING
-- DATA, AND XML METHOD TO SPLIT COMMA SEPARATED VALUES
-- INTO ROWS
CREATE TABLE dbo.EmployeeCity2 (CitySrNo, EmployeeSrNo)
SELECT  SrNo, i.value('.', 'INT')
FROM    (   SELECT  SrNo,
                    x = CONVERT(XML, '<i>' + 
                                REPLACE(EmplSrNo, ',', '</i><i>') + 
                                '</i>')
            FROM    dbo.EmployeeCity
        ) AS t
        CROSS APPLY t.x.nodes('i') rx (i);

-- DROP EXISTING TABLE SO THAT WE CAN CREATE A VIEW OF THE SAME NAME
DROP TABLE dbo.EmployeeCity;
GO

-- CREATE A VIEW THAT REPLICATES THE FORMAT OF THE CURRENT TABLE SO
-- THAT EXISTING SELECT QUERIES ARE NOT AFFECTED
CREATE VIEW dbo.EmployeeCity
AS
    SELECT  c.SrNo,
            c.City,
            EmplSrNo = STUFF((  SELECT  ',' + CAST(EmployeeSrNo AS VARCHAR(10))
                                FROM    dbo.EmployeeCity2 AS ec
                                WHERE   ec.CitySrNo = c.SrNo
                                FOR XML PATH(''), TYPE
                            ).value('.', 'VARCHAR(MAX)'), 1, 1, '')
    FROM    dbo.City AS c;
GO

-- FINALLY, THE QUERY YOU NEED TO GET THE OUTPUT IN THE QUESTION
SELECT  c.SrNo,
        c.City,
        EmployeeName = STUFF((  SELECT  ',' + m.Name
                                FROM    dbo.EmployeeCity2 AS ec
                                        INNER JOIN EmployeeMst AS m
                                            ON m.SrNo = ec.EmployeeSrNo
                                WHERE   ec.CitySrNo = c.SrNo
                                FOR XML PATH(''), TYPE
                            ).value('.', 'VARCHAR(MAX)'), 1, 1, '')
FROM    dbo.City AS c;

You will still need to amend how data is inserted/updated/deleted, however there should be much fewer places where this happens than where it is selected, and all your existing select queries are covered by the view.

For further read on a couple of the principles used above to split the comma separated string, and then put it back together again, see:

great answer, but note that any existing SQL code that inserts or updates EmployeeCity will cease to work. None the less it'll be worth the effort to refactor any such code! — James S, Dec 02 '14 at 12:45
Thanks, i will suggest my superior to do this and make new table. — Hiren gardhariya, Dec 02 '14 at 13:00
i Used same method, now what to do for insert?? i have to change EmployeeCity2 instead of EmployeeCity ? — Hiren gardhariya, Dec 03 '14 at 08:45

Saravana Kumar · Answer 2 · 2014-12-02T12:24:42.123

Use this. Fiddler Demo

  CREATE FUNCTION EmployeeName(@Expr1 AS VARCHAR(MAX))
    RETURNS VARCHAR(MAX)
    BEGIN
        DECLARE @res AS VARCHAR(MAX)

        SET @res =  (SELECT ',' + B.name AS A FROM 
            (SELECT
                 Split.a.value('.', 'VARCHAR(100)') AS CVS  
            FROM  
            (
                SELECT CAST ('<M>' + REPLACE(@Expr1, ',', '</M><M>') + '</M>' AS XML) AS CVS 
            ) AS A CROSS APPLY CVS.nodes ('/M') AS Split(a)) AS A

        INNER JOIN EmployeeMst AS B ON A.CVS = B.id 

        FOR XML PATH(''), TYPE).value('.', 'varchar(max)')

        RETURN STUFF(@res, 1,1,'')
    END 

SELECT srno, city, dbo.EmployeeName(EmplSrNo) AS EmployeeName FROM EmployeeCity

Vasily · Answer 3 · 2014-12-03T00:20:56.420

[SQL Fiddle][1]
--------------------------------------------------------
--create temp table for using in split
IF OBJECT_ID('tempdb..#Tally') IS NOT NULL 
    DROP TABLE #Tally
CREATE TABLE #Tally ( N INT )
DECLARE @i AS INT = 1
WHILE @i != 1000 
    BEGIN
        INSERT  INTO #Tally
                ( N )
        VALUES  ( @i )
        SET @i = @i + 1
    END
--------------------------------------------------------
--Create temp table EmployeeMst
IF OBJECT_ID('tempdb..#EmployeeMst') IS NOT NULL 
    DROP TABLE #EmployeeMst
CREATE TABLE #EmployeeMst
    (
      SrNo INT ,
      EmpNo VARCHAR(10),
    )
INSERT  INTO #EmployeeMst
VALUES  ( 1, 'abc' ),
        ( 2, 'xyz' ),
        ( 3, 'pqr' ),
        ( 4, 'def' )
--------------------------------------------------------
--Create temp table EmployeeCity
IF OBJECT_ID('tempdb..#EmployeeCity') IS NOT NULL 
    DROP TABLE #EmployeeCity
CREATE TABLE #EmployeeCity
    (
      srno INT ,
      City VARCHAR(20) ,
      EmplSrNo VARCHAR(MAX)
    )
INSERT  INTO #EmployeeCity
VALUES  ( 1, 'Delhi', '1,2,3,4' ),
        ( 2, 'Mumbai', '2,3,1' ),
        ( 3, 'New York', '3,2' )
--------------------------------------------------------
--Get output as in example 
;
WITH    EmployeeCityUpdated
          AS ( SELECT   E.srno ,
                        E.City ,
                        CONVERT(INT, REPLACE(f4.EmpNoUpd, ',', '')) AS EmployeeName
               FROM     #EmployeeCity AS E
                        JOIN #Tally AS T ON SUBSTRING(',' + E.EmplSrNo, T.N, 1) = ','
                                            AND T.N < LEN(EmplSrNo) + 1
                        CROSS APPLY ( SELECT    string = SUBSTRING(' '
                                                              + EmplSrNo + ',',
                                                              T.N + 1,
                                                              LEN(EmplSrNo)
                                                              + 1)
                                    ) f1
                        CROSS APPLY ( SELECT    p1 = CHARINDEX(',', string)
                                    ) f2
                        CROSS APPLY ( SELECT    EmpNoUpd = SUBSTRING(EmplSrNo,
                                                              T.N, p1)
                                    ) f4
             )
    SELECT  EC.srno ,
            EC.City ,
            SUBSTRING(( SELECT  ',' + CONVERT(VARCHAR, EM.EmpNo)
                        FROM    EmployeeCityUpdated AS ECU
                                JOIN #EmployeeMst AS EM ON ECU.EmployeeName = EM.SrNo
                        WHERE   ECU.srno = EC.srno
                      FOR
                        XML PATH('')
                      ), 2, 1000) AS EmployeeName
    FROM    #EmployeeCity AS EC
--------------------------------------------------------
--srno City          EmployeeName
--1    Delhi         abc,xyz,pqr,def
--2    Mumbai        xyz,pqr,abc
--3    New York      pqr,xyz

Replace comma separated values in SQL Server

3 Answers3