SQL Argument data type int is invalid for argument 1 of charindex function

Question

I'm trying to convert MySQL query to MsSql query but I'm having trouble. Here is my query:

MySQL

SELECT *,
(SELECT count(books.id)
   FROM books
   WHERE books.status = 1
     AND FIND_IN_SET(categories.id, books.multiple_category_id)) AS book_count
   FROM categories, books
   WHERE categories.parent_id=0
     AND categories.status=1 ;

SQL I've tried

SELECT *,
  (SELECT count(books.id)
   FROM books
   WHERE books.status = 1
     AND CHARINDEX(categories.id, books.multiple_category_id) > 0) AS book_count
   FROM categories, books
   WHERE categories.parent_id=0
     AND categories.status=1 ;

The errors I'm getting are:

Argument data type int is invalid for argument 1 of charindex function.

For reference. http://sqlfiddle.com/#!3/4ed19/3

does anyone have any ideas? Thanks in advance!

Answer 1

Use this query. I just changed categories.id to CAST(categories.id AS VARCHAR). Because the id is integer in categories table.

SELECT *,
(SELECT count(books.id)
   FROM books
   WHERE books.status = 1
     AND CHARINDEX(CAST(categories.id AS VARCHAR), books.multiple_category_id) > 0) AS book_count
FROM categories,
     books
WHERE categories.parent_id=0
  AND categories.status=1 ;

Answer 2

MS-SQL's CHARINDEX function requires VARCHARs or NVARCHARs as parameters, so your categories.id should be converted to VARCHAR. Something like this:

CHARINDEX(CAST(categories.id AS VARCHAR(16)), books.multiple_category_id ) > 0 )

However, I would consider refactoring both tables and your query:

1. Query refactoring

As it is written, you can have false matches. E.g. categories.id = 1 and your multiple_category_id is '11,12'. An alternative is to split your string is to have your condition like this:

AND EXISTS (SELECT 1 FROM dbo.SplitString(books.multiple_category_id) WHERE Token = categories.id)

Also, * should be replaced with actually needed columns (in this case, it will bring all the columns in both tables). Something like this:

2. Table refactoring

Instead of parsing strings you should define an X table like this:

categoryXbook

categoryId FK references category bookId FK references book PRIMARY KEY (categoryId, bookId)

so, that you can do your query using simple JOINs instead of strings searches which can be slower (no indexes can be used).