How to write query where string contains any data from array using WHERE IN()?

Question

If I have table like this

+------+---------------------------+
|  Id  |  City                     |
+------+---------------------------+
|   1  | Vancouver, Miami, New York| 
+------+---------------------------+
|   2  | Vancouver Miami           | 
+------+---------------------------+
|   3  | New York                  | 
+------+---------------------------+
|   4  | Miami                     | 
+------+---------------------------+
|   5  | Vancouver, New York       | 
+------+---------------------------+
|   6  | New York Miami            | 
+------+---------------------------+
|   7  | Vancouver                 | 
+------+---------------------------+

And some user want get data from Miami or Vancouver

How to write query to get data with id 1,2,4,5,6,7

Data with id 3 should not be displayed because there is no Miami or Vancouver in that string

EDIT: Because I am getting Miami and Vancouver in array list I need query using function WHERE IN ()

You cannot use in operator because your data is not normalised. — Shadow, May 02 '19 at 21:44

Pankaj Gadge · Accepted Answer · 2019-05-02T21:57:15.397

1

There are two ways of doing this, both of them don't involve using WHERE IN approach, it's not possible to use wild card matching on multiple values with IN operator.

Use OR statement

SELECT * FROM yourtable WHERE CITY LIKE '%Miami%' OR CITY LIKE '%Vancouver%'
Using REGEXP for Mysql5.0 and above

SELECT * FROM yourtable WHERE CITY REGEXP 'Miami|Vancouver'

edited May 02 '19 at 21:57

answered May 02 '19 at 21:41

Pankaj Gadge

2,748
3
18
25

I just edited my question, sorry for lack of information – Ante Ereš May 02 '19 at 21:42
What if the city is called `York`, not `New York`? – Shadow May 02 '19 at 21:47
Thanks a lot, that helped – Ante Ereš May 02 '19 at 21:54

How to write query where string contains any data from array using WHERE IN()?

1 Answers1