Wildcard list of numbers after comma and join on single value [closed]

I have TABLE A with one of the columns containing a single value and TABLE B with one of the columns containing list of possible matching values.

My code seems to take only first items in the list but does not go deeper within a list to find matching number.

Can you please help me to improve the following code:

select  Logs.SingleValue, 
Instances.list from Logs,Instances
where Logs.Column1=Instances.DeviceNumber and 
 (',' + RTRIM(Instances.list) + ',') LIKE  Logs.SingleValue   

The data in the list looks like

106, 107, 108, 109, 110, 111, 112, 113, 114, 115, 116, 117, 118, 119, 120

or

3346, 3347, 3348, 3349, 3350, 3351, 3352, 3353, 3354, 3355, 3356, 3357, 3358, 3359, 3360

I use SQL within R programming environment; not sure what version it is. I’m not sure if the DBMS is MS SQL Server or ORACLE. All I know is that I have found a similar case and the command did not work so it needs to be handwritten in pure SQL.

Answer

The syntax looks like T-SQL, meaning it’s MS SQL Server.

The best advice I can give you is to normalize your database – get rid of that comma-delimited column and move it to a table.
Read Is storing a delimited list in a database column really that bad?, where you will see a lot of reasons why the answer to this question is Absolutely yes!

If you can’t do that, you should probably change your current SQL code to something like this:

select  Logs.SingleValue, Instances.list 
from Logs
inner join Instances ON Logs.Column1 = Instances.DeviceNumber 
and (', ' + RTRIM(Instances.slotlist2) + ',') LIKE '%, '+ Logs.Column2 +',%'

This way you should be able to get all the records where slotlist2 has the number in Column2 somewhere in the list.
Note the space after the first comma in both sides of the like operator.

Please also note that I’ve changed your implicit join to an explicit join.
Since explicit joins have been a part of ANSI SQL for over 25 years now, and every self-respecting RDBMS supports them; there really is no need to use implicit joins anymore.

Edit: I’ve tested my query, and it seems to be working fine.
You can look at it yourself on rextester.