Checking multiple values in a SQL array

Question

I need to check if one (or more) of several values is present in a SQL array. In its simplest form it would look something like this:

SELECT
  (1, 7, 8) IN (1, 2, 3, 4, 5)
FROM DUAL

Obviously this statement won't work. It only works if I check for just one value in the array. But I need to know if one (not all!) of the values I supply is present in the SQL array. The statement should return TRUE if one or more values are present in the array and FALSE if none is.

I realize I could just add several checks, like this:

SELECT
   1 IN (1, 2, 3, 4, 5)
OR 7 IN (1, 2, 3, 4, 5)
OR 8 IN (1, 2, 3, 4, 5)
FROM DUAL

My programming logic however supplies the values in array format "(1, 7, 8)" and the amount of values in this array differs every time i need to run the SQL statement. It would be very handy if I could somehow just paste this array into my existing SQL statement instead of rebuilding the SQL statement on every run, and create the "OR" statements for every value in the array.

Is there a way to do this? I'm using MySQL btw.

Thanx in advance, keep up the good programming!

Is it possible for your "programming logic" to pass a table since SQL works best with tables, not delimited lists? — JNK
– JNK, Commented Jul 15, 2011 at 20:36
how would this table look like? if it's something like: SELECT (1,7,8) FROM DUAL then that would be possible, but I could just build that into the SQL statement. I still don't see how I could test the values to the SQL array. — Kirk Olson
– Kirk Olson, Commented Jul 15, 2011 at 20:44
In .net something like a datatable or an array. A comma-separated list is not an array, its a string. — JNK
– JNK, Commented Jul 15, 2011 at 20:45
I understand that but I want to paste the values into an existing SQL statement so I convert the array to a comma-separated list. — Kirk Olson
– Kirk Olson, Commented Jul 15, 2011 at 20:48

Dylan · Accepted Answer · 2011-08-11 07:51:41Z

1

You can use REGEXP with comma separated strings. You'll have to convert the values first to strings.

SELECT REPLACE(CONCAT(",", "1, 7, 8", ",")," ", "")
       REGEXP CONCAT(",", REPLACE(REPLACE("1 ,2 , 3 , 4 ,5 " ," ", ""),",", ",|,"), ",")
FROM DUAL

Explanation:

This works only with comma-separated strings, not lists as in your example.
The statement removes any spaces from the strings.
Commas are added at both ends of the strings to avoid partial matches.
In the regex pattern all commas are replaced by comma + pipe-sign + comma, so it works as an OR , while avoiding partial matches.

answered Aug 11, 2011 at 7:51

Dylan

9,40322 gold badges101 silver badges158 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Nicola Cossu · Accepted Answer · 2011-07-15 20:57:56Z

1

Can you use a simple join creating a dynamic/temporary table and making a simple join with the other values?

create table a (
id int);

insert into a values (1),(7),(8);

create table b like a;

insert into b values (1),(2),(3),(4),(5);


select a.* from a
inner join b
on a.id = b.id

edited Jul 15, 2011 at 20:57

answered Jul 15, 2011 at 20:48

Nicola Cossu

56.7k15 gold badges96 silver badges99 bronze badges

3 Comments

Chris Cunningham Over a year ago

I'm curious whether the query plan is the same for your answer and for SELECT a.* from a where a.id IN (SELECT b.id FROM b). Do you think an EXPLAIN will reveal that those two queries are actually identical?

Nicola Cossu Over a year ago

@ Kirk Olson. You're welcome. @ Chris Cunningham. I usually tend to avoid IN clause because AFAIK it's slower with mysql version < 5.5 but I'm not a sql guru about query optimization like Quassnoi and other SO users ;)

Chris Cunningham Over a year ago

@nick rulez I'm hoping that Kirk will run the EXPLAINS for us and let us know. There's still time to become an expert!

Collectives™ on Stack Overflow

Checking multiple values in a SQL array

2 Answers 2

Comments

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related