SQL Server 2017 (Linux)

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

CREATE TABLE A (
  id nvarchar(10),
  contractnb nvarchar(50),
  otherflags nvarchar(5));
INSERT INTO A VALUES
(1,'11111','blue'),(2,'22222','blue'),(3,'4444444','blue'),(4,'5555555','blue'),
(5,'11111,22222,33333,44444,55555','blue');

5 rows affected

CREATE TABLE B( 
  paramsid int ,
  contracts nvarchar(255),
  otherflags nvarchar(5)  );
INSERT INTO B VALUES (1,'22222','blue');

1 rows affected

SELECT * FROM A;
SELECT * FROM B;


id
contractnb
otherflags


1
11111
blue

2
22222
blue

3
4444444
blue

4
5555555
blue

5
11111,22222,33333,44444,55555
blue




paramsid
contracts
otherflags


1
22222
blue


SELECT * 
from A
JOIN B ON A.otherflags = B.otherflags 
WHERE B.paramsid = 1 
AND A.contractnb like CONCAT('%',B.contracts,'%');


id
contractnb
otherflags
paramsid
contracts
otherflags


2
22222
blue
1
22222
blue

5
11111,22222,33333,44444,55555
blue
1
22222
blue

id	contractnb	otherflags
1	11111	blue
2	22222	blue
3	4444444	blue
4	5555555	blue
5	11111,22222,33333,44444,55555	blue