Problem with IN clause

Question

Problem with IN clause

Navigation

#1 by (2 votes)

2

I need to use the IN clause in a situation where the in part is a string.

Example:

SELECT * FROM BLABLABLA WHERE 4 IN ('6,5,4')

This causes me this error:

Conversion failed when converting the varchar value '6,5,4' to data type int.

In my real case the string '6,5,4' comes from a field of the database itself that I can not modify, so I would have to figure out how to, in my query, convert it so that it stays (6,5,4) or ('6', '5', '4'), so SQL could be run.

I would like to know how to solve this problem without damaging the performance.

sql sql-server

asked by anonymous 23.05.2016 / 16:04

1 answer

Is it possible to customize the return of laravel validation when I use the Request? Laravel Much to Many?

score 2 · Accepted Answer

The solution I found was to use LIKE and commas.

SELECT 
   * 
FROM 
    BLABLABLA 
WHERE 
    ',' + '6,5,4' + ',' LIKE '%,' + CAST(4 AS VARCHAR) + ',%'

So the main string is in this format: ', 6,5,4,' and my number will always be in this format ', 5,' leaving the search with LIKE trusted.