SQL Server 2022

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
SELECT *
FROM (VALUES('a;b;c;d;e','c;a;e;i;k','c;a;e'),
            ('d;e;f;g','e;h;i;j;d','e;d'))V(A,B,C)
     CROSS APPLY (SELECT STRING_AGG(BB.value,';') WITHIN GROUP (ORDER BY BB.ordinal) AS C
                  FROM STRING_SPLIT(V.B,';',1) BB
                  WHERE EXISTS (SELECT 1
                                FROM STRING_SPLIT(V.a,';') AA
                                WHERE BB.value = AA.value))E;


A
B
C
C


a;b;c;d;e
c;a;e;i;k
c;a;e
c;a;e

d;e;f;g
e;h;i;j;d
e;d
e;d


--LEft over silly solution with TRANSLATE if codes were one character in length
SELECT *,
       REPLACE(TRANSLATE(V.B,P.Pattern,REPLICATE('; ',LEN(P.Pattern)/2)),'; ','')
FROM (VALUES('a;b;c;d;e','c;a;e;i;k','c;a;e'),
            ('d;e;f;g','e;h;i;j;d','e;d'))V(A,B,C)
     CROSS APPLY (VALUES(REPLACE(';' + TRANSLATE(V.B, V.A, STUFF(REPLICATE('; ', LEN(';'+V.A)/2),1,1,'')),'; ',''))) P(Pattern)


A
B
C
Pattern
(No column name)


a;b;c;d;e
c;a;e;i;k
c;a;e
;i;k
c;a;e

d;e;f;g
e;h;i;j;d
e;d
;h;i;j
e;d


SELECT *
INTO #YourBadData
FROM (VALUES(1,'a;b;c;d;e','c;a;e;i;k','c;a;e'),
            (2,'d;e;f;g','e;h;i;j;d','e;d'))V(ID,A,B,C);
2 rows affected

CREATE TABLE dbo.TableA (ID int, Code char(1));
CREATE TABLE dbo.TableB (ID int, Code char(1));
CREATE TABLE dbo.RelatedIDs (A int, B int);
INSERT INTO dbo.TableA (ID, Code)
SELECT YBD.ID,
       SS.Value
FROM #YourBadData YBD
     CROSS APPLY STRING_SPLIT(YBD.A,';') SS;
9 rows affected

INSERT INTO dbo.TableB (ID, Code)
SELECT YBD.ID,
       SS.Value
FROM #YourBadData YBD
     CROSS APPLY STRING_SPLIT(YBD.B,';') SS;
10 rows affected

INSERT INTO dbo.RelatedIDs (A,B) --This is overly simplifued
SELECT ID, ID
FROM dbo.#YourBadData;
2 rows affected

SELECT B.ID,
       B.Code
FROM dbo.TableB B
WHERE EXISTS (SELECT 1
              FROM dbo.RelatedIDs RI
                   JOIN dbo.TableA A ON RI.A = A.ID
              WHERE RI.B = B.ID
                AND A.Code = B.Code);


ID
Code


1
c

1
a

1
e

2
e

2
d


DROP TABLE #YourBadData;
DROP TABLE dbo.TableA;
DROP TABLE dbo.TableB;
DROP TABLE RelatedIDs
A	B	C	Pattern	(No column name)
a;b;c;d;e	c;a;e;i;k	c;a;e	;i;k	c;a;e
d;e;f;g	e;h;i;j;d	e;d	;h;i;j	e;d