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.

Answer 1

[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

Answer 2

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

Answer 3

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:

• Splitting strings the right way - or the next best way
• Splitting Strings: A Follow-up
• Splitting Strings : Now with less T-SQL
• String Concatenation with SQL Server