Conversion failed when converting the varchar value '1,' to data type int

Question

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"

What happens if you replace WHERE ID IN with WHERE CAST(ID as varchar(5)) IN ? — Phil C, Nov 27 '10 at 09:42
I agree with @Traroth - list of IDs returned as a string of comma-separated values suggests **a problem with database design**. Is there really the `catIDs` column in the `x` table? Or maybe this is just an example and `1,3,4` is actually returned from stored rpocedure or retrieved from a flat file? — Marek Grzenkowicz, Nov 27 '10 at 10:07
@Carnotaurus: It's working but not returning right result because it's looking for 'x, y', not 'x', 'y' — HasanG, Nov 27 '10 at 10:39
Nice one @Carnotaurus, instead of casting the result of your select into an int cast the ID value to a varchar. Works like a charm. — Ajibola, Dec 12 '11 at 11:32

score 4 · Accepted Answer · edited May 23 '17 at 12:18

4

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.

edited May 23 '17 at 12:18

Community

1
1

answered Nov 27 '10 at 09:57

Marek Grzenkowicz

17,024
9
81
111

Yes, I'm going to use http://stackoverflow.com/questions/314824/t-sql-opposite-to-string-concatenation-how-to-split-string-into-multiple-recor/314917#314917 function. Thank you. – HasanG Nov 27 '10 at 10:42

score 1 · Answer 2 · answered Nov 27 '10 at 09:44

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...

Conversion failed when converting the varchar value '1,' to data type int

2 Answers2