How to use an evaluated expression in SQL IN clause

Question

I have an application that takes a comma separated string for multiple IDs to be used in the 'IN' clause of a SQL query.

    SELECT * FROM TABLE WHERE [TABLENAME].[COLUMNNAME] 
    IN 
    ((SELECT '''' + REPLACE('PARAM(0, Enter ID/IDS. Separate multiple ids by 
    comma., String)', char(44), ''',''') + ''''))

I have tested that PARAM gets the string entered e.g. 'ID1, ID2' but SELECT/REPLACE does not execute. The statement becomes,

    SELECT * FROM TABLE WHERE [TABLENAME].[COLUMNNAME] 
    IN 
    ((SELECT '''' + REPLACE('ID1,ID2', char(44), ''',''') + ''''))

I am trying to achieve,

    SELECT * FROM TABLE WHERE [TABLENAME].[COLUMNNAME] 
    IN ('ID1', 'ID2')

The query does not return any results/errors. I am confident the corresponding records are in the database I am working with. Not sure how to fix this.

Zohar Peled Zohar Peled · Accepted Answer · 2018-04-16T04:09:28

You can't do it like this. The IN operator expects a list of parameters separated by comma, but you supply it with a single parameter that happens to contain a comma delimited string:

If you are working on SQL Server version 2016 or higher, you can use the built in string_split to convert the delimited string into a table.

SELECT * 
FROM TABLE 
WHERE [TABLENAME].[COLUMNNAME] 
IN STRING_SPLIT(@CommaDelimitedString, ',')

For older versions, there are multiple user defined functions you can choose from, my personal favorite is Jeff Moden's DelimitedSplit8K. For more options, read Aaron Bertrand's Split strings the right way – or the next best way.

How to use an evaluated expression in SQL IN clause

1 Answers