- Data Structure
- Networking
- RDBMS
- Operating System
- Java
- MS Excel
- iOS
- HTML
- CSS
- Android
- Python
- C Programming
- C++
- C#
- MongoDB
- MySQL
- Javascript
- PHP
- Physics
- Chemistry
- Biology
- Mathematics
- English
- Economics
- Psychology
- Social Studies
- Fashion Studies
- Legal Studies
- Selected Reading
- UPSC IAS Exams Notes
- Developer's Best Practices
- Questions and Answers
- Effective Resume Writing
- HR Interview Questions
- Computer Glossary
- Who is Who
How to use user variables in MySQL LIKE clause?
Using the CONCAT() function, we can work with user variables in LIKE clause. The syntax is as follows.
set @anyVariableName='anyValue'; select yourColumnName1,yourColumnName2,yourColumnName3,...N from yourTableName whereyourColumnName like CONCAT('%', @anyVariableName, '%');
To understand the above syntax, let us first create a table. The query to create a table is as follows.
mysql> create table UserVariableInLike -> ( -> id int, -> Name varchar(100), -> Age int -> ); Query OK, 0 rows affected (0.83 sec)
Insert records in the table using insert command. The query is as follows.
mysql> insert into UserVariableInLike values(101,'John',23); Query OK, 1 row affected (0.23 sec) mysql> insert into UserVariableInLike values(102,'John Smith',24); Query OK, 1 row affected (0.20 sec) mysql> insert into UserVariableInLike values(103,'Carol Smith',23); Query OK, 1 row affected (0.15 sec) mysql> insert into UserVariableInLike values(104,'Johnson',25); Query OK, 1 row affected (0.20 sec) mysql> insert into UserVariableInLike values(105,'Adam Smith',26); Query OK, 1 row affected (0.21 sec)
Display all records from the table using select statement. The query is as follows
mysql> select *from UserVariableInLike;
The following is the output.
+------+-------------+------+ | id | Name | Age | +------+-------------+------+ | 101 | John | 23 | | 102 | John Smith | 24 | | 103 | Carol Smith | 23 | | 104 | Johnson | 25 | | 105 | Adam Smith | 26 | +------+-------------+------+ 5 rows in set (0.00 sec)
Here is the query which uses the user variable in the LIKE clause. The query is as follows −
mysql> set @searchName='John'; Query OK, 0 rows affected (0.00 sec) mysql> select id,Name,Age from UserVariableInLike where Name like CONCAT('%', @searchName, '%');
The following is the output.
+------+------------+------+ | id | Name | Age | +------+------------+------+ | 101 | John | 23 | | 102 | JohnSmith | 24 | | 104 | Johnson | 25 | +------+------------+------+ 3 rows in set (0.05 sec)
Advertisements