Implement multiple LIKE operators in a single MySQL query
To implement multiple LIKE clauses, the syntax is as follows −
select * from yourTableName
where yourColumnName1 LIKE ('%yourValue1%' or yourColumnName2 LIKE '%yourValue2%') or (yourColumnName3 LIKE '%yourValue3');Let us first create a table −
mysql> create table DemoTable1534
-> (
-> ClientId int NOT NULL AUTO_INCREMENT PRIMARY KEY,
-> ClientName varchar(20),
-> ClientAge int,
-> ClientCountryName varchar(20)
-> );
Query OK, 0 rows affected (0.78 sec)
Insert some records in the table using insert command −
mysql> insert into DemoTable1534(ClientName,ClientAge,ClientCountryName) values('Chris Brown',29,'AUS');
Query OK, 1 row affected (0.21 sec)
mysql> insert into DemoTable1534(ClientName,ClientAge,ClientCountryName) values('David Miller',49,'UK');
Query OK, 1 row affected (0.14 sec)
mysql> insert into DemoTable1534(ClientName,ClientAge,ClientCountryName) values('John Doe',43,'US');
Query OK, 1 row affected (0.22 sec)
mysql> insert into DemoTable1534(ClientName,ClientAge,ClientCountryName) values('Adam Smith',38,'US');
Query OK, 1 row affected (0.12 sec)
mysql> insert into DemoTable1534(ClientName,ClientAge,ClientCountryName) values('Carol Taylor',36,'UK');
Query OK, 1 row affected (0.16 sec)Display all records from the table using select statement −
mysql> select * from DemoTable1534;
This will produce the following output −
+----------+--------------+-----------+-------------------+
| ClientId | ClientName | ClientAge | ClientCountryName |
+----------+--------------+-----------+-------------------+
| 1 | Chris Brown | 29 | AUS |
| 2 | David Miller | 49 | UK |
| 3 | John Doe | 43 | US |
| 4 | Adam Smith | 38 | US |
| 5 | Carol Taylor | 36 | UK |
+----------+--------------+-----------+-------------------+
5 rows in set (0.00 sec)
Following is the query for multiple LIKE operator usage in a single query −
mysql> select * from DemoTable1534
-> where ClientName LIKE ('%Doe%' or ClientAge LIKE '%38%') or (ClientCountryName LIKE '%S');This will produce the following output −
+----------+-------------+-----------+-------------------+
| ClientId | ClientName | ClientAge | ClientCountryName |
+----------+-------------+-----------+-------------------+
| 1 | Chris Brown | 29 | AUS |
| 3 | John Doe | 43 | US |
| 4 | Adam Smith | 38 | US |
+----------+-------------+-----------+-------------------+
3 rows in set, 5 warnings (0.00 sec)
Published on 12-Dec-2019 05:38:56
