SQL Server : inner join String if number with int ID

Question

Is there a way to get the lines through a query where the inner join compares a string of numbers with an integer? The integer is an ID and the string of numbers is the ID of that integer, but in string form.

Query:

SELECT
    CT.ID, CEX.ID AS IDFiled, CAT.Name, CAT.ID AS IDCat 
FROM
    Field_Cat AS CT 
INNER JOIN 
    C_Extra AS CEX WITH(NOLOCK) ON CT.IDCampoExtra = CEX.ID 
INNER JOIN 
    Catg AS CAT WITH(NOLOCK) ON ',' + CT.IDCat + ',' LIKE CAT.ID
WHERE 
    CEX.ID = 1011

CT.IDCat = 4,8,12

I intend to get the rows from the CAT table where the id is 4,8,12.

With this query the result is null. Does anyone know how I can solve the problem?

Fix your data model! Don't store numbers in strings! Don't store multiple values in a string! — Gordon Linoff, Apr 04 '20 at 12:07
Why are you using `NOLOCK` here too? Do you understand the implications of it? — Thom A, Apr 04 '20 at 12:08
Also, you've posted on the English SO community but your title isn't English. Please take the time to post it in English, as otherwise your question may attract unnecessary down or close votes. — Thom A, Apr 04 '20 at 12:09
I highly recommend reading the duplicate as well as the reasons given in @GordonLinoff answer as to why storing data in comma separated lists is bad. — Nick, Apr 04 '20 at 12:20

score 3 · Accepted Answer · answered Apr 04 '20 at 12:09

One technical solution is to use LIKE:

ON CONCAT(',', CT.IDCat, ',') LIKE CONCAT('%,', CAT.ID, ',%')

However, the real answer is to fix your data model. It is bad for the following reasons:

Numbers should be stored as numbers.
Ids should have properly declared foreign key relationships.
Unless you are using JSON or XML, only one value should be stored in a column.
SQL Server has lousy string processing capabilities.
SQL in general has a great way to store lists. It is called a table, not a string.

SQL Server : inner join String if number with int ID

1 Answers1