How to use LIKE operator in SQL for multiple values

LIKE clause allows you to select column values whose value matches specific patterns. Generally, database users use a single LIKE clause in SELECT queries but sometimes you may want to use LIKE operator in SQL for multiple values. In this article, we will learn how to do this.

How to use LIKE operator in SQL for multiple values

Let us say you have the following table sales(id, product, location, amount)

mysql> create table sales(id int, 
        product varchar(10), 
        location varchar(10), 
        amount int);

mysql> insert into sales(id, product, location, amount)
       values(1, 'ABC', 'NYC', 150),
             (2, 'DEF', 'SFO', 180),
             (3, 'CBC', 'NYC', 200);

mysql> select * from sales;
+------+---------+----------+--------+
| id   | product | location | amount |
+------+---------+----------+--------+
|    1 | ABC     | NYC      |    150 |
|    2 | DEF     | SFO      |    180 |
|    3 | CBC     | NYC      |    200 |
+------+---------+----------+--------+

If you want to select rows where location ends with ‘YC’ here is the query for it.

mysql> select * from sales where location like '%YC%';
+------+---------+----------+--------+
| id   | product | location | amount |
+------+---------+----------+--------+
|    1 | ABC     | NYC      |    150 |
|    3 | CBC     | NYC      |    200 |
+------+---------+----------+--------+

But if you want to select rows where location ends with ‘YC’ or location ends with ‘BC’ then generally people use the following query.

mysql> select * from sales where location like '%YC%' or '%BC%';

But the above query will give an error. Instead you need to separately mention the LIKE clause twice in each sub clause of OR statement.

mysql> select * from sales where location like '%YC%' or location like '%BC%';

Similarly, if you want to select rows where location ends with ‘YC’ or product ends with ‘BC’ then use the following query.

mysql> select * from sales where location like '%YC%' or product like '%BC%';
+------+---------+----------+--------+
| id   | product | location | amount |
+------+---------+----------+--------+
|    1 | ABC     | NYC      |    150 |
|    3 | CBC     | NYC      |    200 |
+------+---------+----------+--------+

Alternatively, you can also use regular expression to select rows matching multiple patterns, as shown below.

mysql> select * from sales where location regexp 'YC|BC';

In this article, we have learnt how to use LIKE operator for multiple values in MySQL. The key is to mention separate LIKE clauses for each column that you are matching.

Also read:

How to Increase Import File Size Limit in PHPMyAdmin
How to Add Column After Another Column in MySQL
How to Retrieve MySQL Username & Password
How to Find & Replace Text in Entire Table in MySQL
How to Take Backup of Single Table in MySQL