sql server - Return rows where ID is in semicolon separated string from subquery MSSQL

Question

I'm trying to query my sql database to return all the rows where the ID is contained in a separate tables column. The list of project IDs is kept in the Feedback table in the Project_ID Column with datatype varchar. I am trying to return the rows from the Projects table where the IDs are kept in the Project_ID column with datatype varchar.

I am doing this using the query

SELECT * FROM Projects WHERE Project_ID IN (
    SELECT Project_ID FROM Feedback WHERE ID = 268 and Project_ID IS NOT NULL
)

When I run this query I am returned with the message:

Conversion failed when converting the varchar value '36;10;59' to data type int

Answer 1

This is yet another example of the importance of normalizing your data.
Keeping multiple data points in a single column is almost never the correct design, and by almost never I mean about 99.9999%.

If you can't normalize your database, you can use a workaround like this:

SELECT * 
FROM Projects p
WHERE EXISTS (
    SELECT Project_ID 
    FROM Feedback F WHERE ID = 268 
    AND Project_ID IS NOT NULL
    AND ';'+ F.Project_ID +';' LIKE '%;'+ CAST(p.Project_ID as varchar) +';%'
)

You can't use the IN operator since it's expecting a list of values delimited by a comma, while you try to supply it with a single value that is delimited by a semicolon. Even if the values in Project_ID was delimited by a comma it would still not work.

The reason I've added the ; on each side of the Project_ID in both tables is that this way the LIKE operator will return true for any location it finds the Projects.Project_Id inside the Feedback.Project_Id. You must add the ; to the Projects.Project_Id to prevent the LIKE to return true when you are looking for a number that is a partial match to the numbers in the delimited string. Consider looking for 12 in a string containing 1;112;455 - without adding the delimiter to the search value (12 in this example) the LIKE operator would return true.