MSSQL WHERE A.field1 IN B.field2 when field2 format is '11111','22222','33333','55555'

Question

I have a weird issue to solve and I am not sure what would be the best way to do this from a performance perspective as I have millions of records to parse in table A (but only 1 row in table B per query). Table B is coming from a UI selectbox.

TABLE A id nvarchar(10) contractnb nvarchar(8) -- '22222', otherflags = 'blue'

TABLE B paramsid int contracts(255) -- '11111','22222','4444444','5555555' otherflags nvarchar(5)

I need to

SELECT * from A, B WHERE B.paramsid = 1 AND A.otherflags = B.otherflags AND A.contractnb IN B.contracts --THIS IS THE ISSUE

I was thinking to use LIKE and do something like this below but I think performance wise it is not a good idea : AND (%A.contractnb%) LIKE B.contracts

But all of this is not working. Any help / input would be quite appreciated. Thanks

Answer 1

This can be made to work with LIKE as follows, but as mentioned in the comment it is not the most efficient solution. (If we're talking about a few hundred lines it's not going to be a problem.)
See the dbFiddle link at the bottom for creation of test schema.

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      

db<>fiddle here

Answer 2

if tableB is only one row, you can use a solution like this

insert into tableA values (1, 'blue', 22222)
insert into tableB values (1, '11111,22222,4444444,5555555', 'blue')
 
 
select b.otherflags,
       value,
       a.*
from   tableB b
  outer apply string_split(b.contracts, ',')
  left join tableA a on value = a.otherflags
where b.paramsid = 1

But when tableB can have many rows and performance is too bad, consider moving the data from tableB into another table which is normalized

DBFiddle

otherflags	value	id	contractnb	otherflags_1
blue	11111
blue	22222	1	blue	22222
blue	4444444
blue	5555555

Answer 3

First of all you can't use IN clause in this case because you have two strings but the IN works with lists of values:

'val1' IN ('val2', 'val3', 'val1')

For your question you can use LIKE and CHARINDEX with COLLATION Latin1_General_BIN option.

select * 
from A, B
where ',' + B.contracts + ',' collate Latin1_General_BIN like '%,' + A.contractnb + ',%' collate Latin1_General_BIN 

select * 
from A, B
where charindex(',' + A.contractnb + ',' collate Latin1_General_BIN, ',' + B.contracts + ',' collate Latin1_General_BIN)>0

This makes LIKE and CHARINDEX much more fast, but pay attention because it is a CASE SENSITIVE match.

See this answer for complete analysis.