SQL String join to table

Question

Given few strings as

SET @Codes1 = 3,4
SET @Codes2 = 1
SET @Codes3 = --empty

Table -- TblCode

Id   Code
1     A 
2     B
3     C
4     D

How to convert the @Codes1, @Codes2, @Codes3 with join to the table TblCode so it returns the following output :

1. @Codes1 = CD
2. @Codes2 = A
3. @Codes3 = --empty

Note that the concatenation for the output is without the comma.

PS - This is a small example to a much larger and complex data set. Kindly ignore any wrongful design pattern here.

Don't store codes in comma-delimited strings. Your code will be much simpler. — Gordon Linoff, Oct 11 '17 at 11:42
@GordonLinoff, I understand that completely. It has been this way for past many years. Can't help it. I am not sure if you remember but you answered a similar question before -- https://stackoverflow.com/questions/44415270/sql-string-combine-based-on-id — Sajal, Oct 11 '17 at 11:45
. . That question is different (and it has the same reaction to the data format). It is not trying to do the replace in place. You should really fix your data format. — Gordon Linoff, Oct 11 '17 at 12:08
That is not possible currently, Is it achievable with the given data format? — Sajal, Oct 11 '17 at 12:10

score 3 · Accepted Answer · answered Oct 11 '17 at 12:18

You can try this. I added the answer just for @Codes1, but it works with @Codes2 and @Codes3 too.

DECLARE @TblCode TABLE (Id INT,   Code VARCHAR(2))
INSERT INTO @TblCode
VALUES(1, 'A'), 
(2,'B'),
(3,'C'),
(4,'D')

DECLARE @Codes1 VARCHAR(10) = '3,4'
DECLARE @Codes2 VARCHAR(10) = '1'
DECLARE @Codes3 VARCHAR(10) = NULL

DECLARE @CodesOut VARCHAR(10) = ''


;WITH CTE_1 AS (
    SELECT CODE= @Codes1 + ','
)
, CTE_2 AS -- It silit text to rows
(
    SELECT RIGHT(CTE_1.CODE, LEN(CTE_1.CODE) - CHARINDEX(',',CTE_1.CODE)) CODE , SUBSTRING(CTE_1.CODE, 0, CHARINDEX(',',CTE_1.CODE)) ID, CHARINDEX(',',CTE_1.CODE) AS CI 
    FROM CTE_1 
    UNION ALL
    SELECT RIGHT(CTE_2.CODE, LEN(CTE_2.CODE) - CHARINDEX(',',CTE_2.CODE)) CODE , SUBSTRING(CTE_2.CODE, 0, CHARINDEX(',',CTE_2.CODE)) ID, CHARINDEX(',',CTE_2.CODE) AS CI
    FROM CTE_2  WHERE LEN(CTE_2.CODE) > 0
)
SELECT @CodesOut = @CodesOut + C.Code FROM CTE_2 INNER JOIN @TblCode C ON CTE_2.ID = C.Id

SELECT @CodesOut

Result:

CD

score 1 · Answer 2 · answered Oct 11 '17 at 12:20

You can use a recursive CTE. Here is one method:

with c as (
      select c.*, row_number() over (partition by id) as seqnum
      from c
     ),
     cte as (
      select cast(@codes as varchar(max)) as str,
             replace(@codes, id, code) as newstr,
             1 as lev
      from c
      where seqnum = 1
      union all
      select str, replace(newstr, id, code), lev + 1
      from cte join
           c
           on c.seqnum = cte.lev + 1
     )
select top (1) newstr
from cte
order by lev desc;

If there is an error in the syntax, set up a SQL Fiddle or Rextester or something similar so it can be fixed.

SQL String join to table

2 Answers2