MySQL LIKE IN()?

You can implement MySQL Like IN() with the help of Regular Expression (regexp) as well. The syntax is as follows −

select *from yourTableName where yourColumName regexp ‘value1|value2|value3……|valueN’;

To understand the above logic, you need to create a table. Let us first create a table −

mysql> create table INDemo
   -> (
   -> Id int,
   -> Name varchar(100)
   -> );
Query OK, 0 rows affected (0.90 sec)

Insert some records into the table. The query is as follows −

mysql> insert into INDemo values(100,'John');
Query OK, 1 row affected (0.13 sec)
mysql> insert into INDemo values(104,'Carol');
Query OK, 1 row affected (0.18 sec)
mysql> insert into INDemo values(108,'David');
Query OK, 1 row affected (0.19 sec)
mysql> insert into INDemo values(112,'Smith');
Query OK, 1 row affected (0.12 sec)
mysql> insert into INDemo values(116,'Johnson');
Query OK, 1 row affected (0.17 sec)
mysql> insert into INDemo values(120,'Sam');
Query OK, 1 row affected (0.16 sec)

Now we can display all the records with the help of SELECT statement. The query is as follows −

mysql> select *from INDemo;

The following is the output −

+------+---------+
| Id   | Name    |
+------+---------+
| 100  | John    |  
| 104  | Carol   |
| 108  | David   |
| 112  | Smith   |
| 116  | Johnson |
| 120  | Sam     |
+------+---------+
6 rows in set (0.00 sec)

Use regexp that works like IN(). You can apply the above syntax which I have discussed in the beginning. The query is as follows −

mysql> select *from INDemo where Id regexp '112|116|100';

The following is the output −

+------+---------+
| Id   | Name    |
+------+---------+
| 100  | John    |
| 112  | Smith   |
| 116  | Johnson |
+------+---------+
3 rows in set (0.21 sec)

You will get the same output with IN(). Now, let us check it with the help of IN(). The query is as follows −

mysql> select *from INDemo where Id IN(112,116,100);

Here is the output

+------+---------+
| Id   | Name    |
+------+---------+
| 100  | John    |
| 112  | Smith   |
| 116  | Johnson |
+------+---------+
3 rows in set (0.00 sec)

As you can see in the above output, we are getting the same result.

Arjun Thakur

Published on 21-Dec-2018 12:22:35

• Related Questions & Answers
• Query for implementing MySQL LIKE as MySQL IN?
• Use MySQL LIKE and NOT LIKE to display similar results?
• How to ORDER BY LIKE in MySQL?
• Display different variables in MySQL using LIKE?
• Is there anything like substr_replace in MySQL?
• MySQL LIKE query with dynamic array?
• How to order by a custom rule like order like 4,2,1,3 in MySQL?
• Using LIKE for two where clauses in MySQL?
• Can we combine MySQL IN AND LIKE operators?
• Using LIKE clause twice in a MySQL query
• Multiple LIKE Operators with ORDER BY in MySQL?
• Can we use LIKE and OR together in MySql?
• How to use user variables in MySQL LIKE clause?
• SHOW TABLE statement with multiple LIKE values in MySQL?
• Implement multiple LIKE operators in a single MySQL query