Problem with clause IN

Asked

Viewed 48 times

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')

It causes me this mistake:

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 cannot modify, so I would have to find a way to, in my query, convert it to be (6,5,4) or ('6','5','4'), so SQL could be run.

I would like to know a way to solve this problem without denying the performance too much.

sql sql-server

  • https://www.brentozar.com/archive/2016/03/splitting-strings-sql-server-2016-rescue/. Does this help?

  • I think it would be perfect, but from what I understood (and tested unsuccessfully) this STRING_SPLIT is a function of the new SQL Server 2016, I’m using a previous version.

  • Can’t be: SELECT * FROM BLA WHERE field LIKE '%4%'

  • Not because I would also take 34, 44 for example.

  • Hmmm. But if IN were accepted '34', '44' and '6.5.4' it would be selected as well. No?

1 answer

2


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 the reliable LIKE.

Browser other questions tagged sql sql-server

You are not signed in. Login or sign up in order to post.