By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
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
CREATE TABLE T(
Category varchar(50)
);
INSERT INTO T VALUES ('Delivery;Gauges;Book;Table');
CREATE TABLE T1(
id INT,
Category varchar(50)
);
INSERT INTO T1 VALUES (13183,'Delivery');
INSERT INTO T1 VALUES (88781,'Gauges');
INSERT INTO T1 VALUES (88782,'Book');
INSERT INTO T1 VALUES (12512,'Table');
;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, '')
| (No column name) |
|---|
| 12512;13183;88781;88782 |