Sql Server: Replace Values In Field Via Lookup Other Table

I am currently running into a challenge that probably has a easy solution, but somehow I am not able to come up with it. I have Table A with two fields that are formatted as follo

Solution 1:

Simple way is by converting the Table A [Codes] column data(csv) into separate rows. Then join with the Table B to get the respective codes. Finally convert the rows to CSV to get result. Try this.

CREATETABLE #tablea
  ([ID]    INT,[Codes] VARCHAR(100))

INSERTINTO #tablea
VALUES      (1,'A;B'),(2,'D' ),(3,'A;C')

CREATETABLE #tableB
  ([ID]    VARCHAR(100),[Codes] VARCHAR(100))

INSERTINTO #tableb
VALUES      ('A','Apple'),( 'B','Orange' ),
            ('C','Pear'),('D','Strawberry')

SELECT a.id,
       a.Codes old_code,
       b.Codes Parsed_Codes
INTO   #finalFROM   #tableb b
       JOIN (SELECT id,
                    codes,
                    Split.a.value('.', 'VARCHAR(100)') [new_Codes]
             FROM   (SELECT id,
                            [Codes],
                            Cast ('<M>'+ Replace([Codes], ';', '</M><M>')
                                  +'</M>'AS XML) AS Data
                     FROM   #tablea) AS A
                    CROSS APPLY Data.nodes ('/M') AS Split(a)) a
         ON a.new_Codes = b.id

SELECT t1.ID,
       old_code,
       Stuff((SELECT'; '+CONVERT(VARCHAR, Parsed_Codes)
              FROM   #final b
              WHERE  b.ID = t1.ID
              FOR XML PATH('')), 1, 2, '')
FROM   #final t1
GROUPBY t1.id,
          old_code 

OUTPUT

ID  old_code    Parsed_Codes
--  --------    ------------
1A;B         Apple; Orange
2   D           Strawberry
3A;C         Apple; Pear

(Note: temp table can be avoided to avoid code confusion i used temp table)

Solution 2:

First create this function.. It's used to split a delimited string, into a variable table. Then we would be able to use this function to determine the codes from the other table and return them as one string using the STUFF function.

CREATEFUNCTION [dbo].[fnSplitString]
   (
    @string NVarchar(MAX)
   ,@delimiterChar(1) =','
   )
RETURNS@tTABLE (string NVarchar(MAX))
ASBEGINDECLARE@posIntDECLARE@pieceVarchar(500)

   IF RIGHT(RTRIM(@string), 1) <>@delimiterSET@string=@string+@delimiterSET@pos= PATINDEX('%'+@delimiter+'%', @string)
   WHILE @pos<>0BEGINSET@piece=LEFT(@string, @pos-1) 
         INSERT@tSELECT@pieceSET@string= STUFF(@string, 1, @pos, '')
         SET@pos= PATINDEX('%'+@delimiter+'%', @string)
      ENDRETURNEND

Then run the following query...

DECLARE@resultTABLE
   (
    [ID] Int
   ,[CommaDelimitedCodes] Varchar(500)
   )
DECLARE@codesTABLE
   (
    [ID] Varchar(500)
   ,[FullNames] Varchar(500)
   )
INSERTINTO@resultSELECT1
                 ,'A;B'INSERTINTO@resultSELECT2
                 ,'D'INSERTINTO@resultSELECT3
                 ,'A;C'INSERTINTO@codesSELECT'A'
                 ,'Apple'INSERTINTO@codesSELECT'B'
                 ,'Orange'INSERTINTO@codesSELECT'C'
                 ,'Pear'INSERTINTO@codesSELECT'D'
                 ,'Strawberry'SELECT* 
        ,STUFF((
                SELECT', '+ [FullNames]
                FROM@codes t
                WHERE   id IN (SELECT*FROM    dbo.fnSplitString(r.[CommaDelimitedCodes], ';'))
               FOR
                XML PATH('')
               ), 1, 2, '') AS taglist
FROM@resultAS r  

I created two variable tables to test - but obviously in your case you'd need to replace those with actual field names in your tables.