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

You are right, `LIKE` will perform horribly when used like that. This is one of many reasons a row should only have one value in each column. Fix the design, or you're stuck with terrible queries like this — HoneyBadger, Mar 16 '22 at 13:29
I don't control the input. Unfortunately, this is often the life of a integration techie. — mkirouac, Mar 16 '22 at 13:34
Considering you only have one row in B, you can normalize that row into a new (temporary) table — HoneyBadger, Mar 16 '22 at 13:35
Evolve! No one should be using [old-style joins](https://sqlblog.org/2009/10/08/bad-habits-to-kick-using-old-style-joins) — SMor, Mar 16 '22 at 13:37
[How bad is storing a comma-delimited list in a database](https://stackoverflow.com/questions/3653462/is-storing-a-delimited-list-in-a-database-column-really-that-bad) — Stu, Mar 16 '22 at 13:37
STRING_SPLIT seems to be doing the job and if performance is an issue I could indeed output it in a temp table before batch process https://learn.microsoft.com/en-us/sql/t-sql/functions/string-split-transact-sql?view=sql-server-ver15 — mkirouac, Mar 16 '22 at 13:51

score 0 · Answer 1 · answered Mar 16 '22 at 14:00

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

GuidoG · Answer 2 · 2022-03-16T14:08:34.790

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

score 0 · Answer 3 · answered Mar 16 '22 at 14:27

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.

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

3 Answers3