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!

Stop right there. Storing CSV in your SQL tables is generally a bad idea. Trying to search one CSV list against another CSV list is particularly not a good idea. Try to normalize your data so that you may take advantage of the native SQL functions. — Tim Biegeleisen, Jul 03 '20 at 06:23
The error is because the separator you have provided has a length of 0 rather than 1 (`''`). If you want to split on a comma, then you need to pass a comma as your argument for the separator, e.g. `string_split(@input,',')`. However, this won't work anyway, you would also need to split your column data, and this all together is an awful approach. Your east/west values would be much better stored in a separate table — GarethD, Jul 03 '20 at 06:26
@GarethD thanks for identifying the syntax error. I have updated my question. How can we split csv column data into a `temp` table and query accordingly? — sridharnetha, Jul 03 '20 at 06:30
what is the expected output, if the user supplies (10,22) and eastzone is stored as (17,10,8,79,74,22) — George Joseph, Jul 03 '20 at 06:35

score 2 · Answer 1 · answered Jul 03 '20 at 06:44

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

score 0 · Answer 2 · answered Jul 03 '20 at 12:16

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.

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

2 Answers2