Conversion failed when converting the varchar value '1,' to data type int
Solution 1
You need to split the 1,3,4
string returned by the subquery into separate int
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:
SELECT ID, Title
FROM c
WHERE ID IN
(
SELECT s
FROM dbo.Split(',', '1,3,4')
)
I replaced the subquery with example results 1,3,4
to shorten the query and make it easier to understand.
Solution 2
If I get it right, you actually have values like "1,3,4" in your column catIDs. So you should extract a substring in the select of your subquery.
By the way, I'm not an MS SQL Server expert, but it's probably a bad database design to do so. I'm not sure the RDBMS engine will use indexes in such a case...
HasanG
I love stack overflow! :) Hasan Gürsoy @ innovacube "As I always say; Every solution creates a new problem"
Updated on June 04, 2022Comments
-
HasanG almost 2 years
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"