How to retrieve records using comma separated values with IN clause?

Question

I would like to retrieve certain records from a full list of table. Here I am using comma separated values with IN clause. The table rows looks like this:

Here is my SQL query, but the query completed with empty result set`

DECLARE @input VARCHAR(1000) = '2,3,17,10,16'
SELECT * FROM locations
WHERE
  east_zone in (SELECT VALUE FROM string_split(@input,','))
OR 
west_zone in (SELECT VALUE FROM string_split(@input,','))

Appreciate your help!

Answer 1

While this can be accomplished, i would request you to rethink your data model. Its a bad idea to store a comma separated list of ids/references in your databases. I strongly am with the comments of Tim Biegeleisen

Alternative would be store the list of zones-titles in a separate table.

Here is a way to accomplish this

with data
  as (select 'model_check_holding' as col1,'1,2,3,4,5' as str union all
      select 'model_cash_holding'  as col1,'5,8,9' as str 
     )
    ,split_data
     as (select *
           from data
         cross apply string_split(str,',') 
        )
     ,user_input
      as(select '2,8,1' as input_val)
select *
 from split_data
where value in (select x.value 
                 from user_input 
                 cross apply string_split(input_val,',') x
                ) 
+---------------------+-----------+-------+
|        col1         |    str    | value |
+---------------------+-----------+-------+
| model_check_holding | 1,2,3,4,5 |     1 |
| model_check_holding | 1,2,3,4,5 |     2 |
| model_cash_holding  |     5,8,9 |     8 |
+---------------------+-----------+-------+

dbfiddle link https://dbfiddle.uk/?rdbms=sqlserver_2019&fiddle=1cc9b224e443369744df19c1d7a7d789

Answer 2

Tim is 110% correct. Your data model is totally messed up -- not only storing multiple values in a delimited string. But also string numbers as strings. Wrong, wrong, wrong.

But if you are stuck with some else's really, really, really bad design choices, you do have an option:

DECLARE @input VARCHAR(1000) = '2,3,17,10,16';

SELECT l.*
FROM locations l
WHERE EXISTS (SELECT 1
              FROM string_split(@input, ',') s1 JOIN
                   string_split(concat(l.east_zone, ',', l.west_zone), ',') l
                   ON s1.value = l.value
             );

I do not recommend this approach. I merely suggest it as a stop-gap until you can fix the data model.