I have table categories (c) and an another table (x) with a column which can contain cat IDs separated with comma as varchar data type. I want to Select related categories but I'm having error "Conversion failed when converting the varchar value '5,' to data type int." when trying to select:
SELECT ID, Title FROM c WHERE ID IN (SELECT catIDs FROM x WHERE ID=any);
The subquery returns data like "1,3,4"
Best Answer
You need to split the
1,3,4
string returned by the subquery into separateint
values. SQL Server does not have a built-in function to do it, but you can use this user-defined function.Create the function
dbo.Split
in your database and then re-write your query as follows:I replaced the subquery with example results
1,3,4
to shorten the query and make it easier to understand.