Replace columns separated by string with id from another table - SQL Server

Question

I have following 2 tables in SQL Server

Category table:

Category
--------------------------
Delivery;Gauges;Book;Table

Category id:

id      name
-----------------
13183   Delivery
88781   Gauges
88782   Book
12512   Table

Intended result is to have category table replaced with category id, as:

Category
-----------------------
13183;88781;88782;12512

I approached this by first separating category columns into separate columns using :

ltrim(rtrim(xDim.value('/x[1]','varchar(max)')))
ltrim(rtrim(xDim.value('/x[2]','varchar(max)')))

and so on. Then used left join and replace on each new column. Isn't there an easier way to do this? I searched on the net and stackoverflow but can't seem to find anything similar.

Possible duplicate of [How to make a query with group\_concat in sql server](https://stackoverflow.com/questions/17591490/how-to-make-a-query-with-group-concat-in-sql-server) — S3S, Nov 14 '18 at 01:02

D-Shih · Accepted Answer · 2018-11-14T01:54:47.810

You can try to make a function to split your string value by a character.

CREATE FUNCTION Split_fun 
( @Words nvarchar(MAX)
, @splitStr varchar(50) 
)
RETURNS @Result_Table TABLE
       (
         [word] nvarchar(max) NULL
       )
BEGIN 
    Declare @TempStr nvarchar(MAX)

    WHILE (CHARINDEX(@splitStr,@Words)>0)
    BEGIN
        Set @TempStr=SUBSTRING(@Words,1,CHARINDEX(@splitStr,@Words)-1)
        Insert into @Result_Table (word) Values (@TempStr)

        Set @Words = REPLACE(@Words,@TempStr+@splitStr,'')
    END/*End While*/

    IF(LEN(RTRIM(LTRIM(@Words)))>0 And CHARINDEX(@splitStr,RTRIM(LTRIM(@Words)))=0) 
    Begin
        Set @TempStr=@Words 

        Insert into @Result_Table (word) Values (@TempStr)

    End 

   RETURN 
END

you can use this function to make a result set by ';'.
do self-join with Category id table.

final you can use FOR XML connect all string by ; to get your expectation result.

;with cte as (
  SELECT id
  FROM T CROSS APPLY Split_fun(Category,';') v 
  JOIN T1 on v.word = t1.Category
)
select STUFF((
select distinct ';'+ cast(id as varchar(10)) 
FROM cte
FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 1, '')

sqlfiddle

Replace columns separated by string with id from another table - SQL Server

1 Answers1