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
What are the different wildcard characters that can be used with MySQL LIKE operator?
As we know that LIKE operator is used along with WILDCARD characters to get the string having specified string. Basically, WILDCARD is the characters that help search data matching complex criteria. Followings are the types of wildcard which can be used in conjunction with LIKE operator
% -The Percentage
The ‘%’ wildcard is used to specify a pattern of 0, 1 or more characters. A basic syntax for using % wildcard is as follows
Select Statement…Where column_name LIKE ‘X%’
Here, X, is any specified starting pattern such as the single character of more and % matches any number of characters starting from 0.
The percentage ‘%’ wildcard can be in many ways with the specified pattern. The followings are some examples showing different LIKE operators with %. Here in these examples X represents the specified pattern.
1. LIKE ‘X%’:- It will find any values that start with “X”.
Example
mysql> Select * from Student Where name LIKE 'a%'; +------+-------+---------+---------+ | Id | Name | Address | Subject | +------+-------+---------+---------+ | 2 | Aarav | Mumbai | History | +------+-------+---------+---------+ 1 row in set (0.00 sec)
2. LIKE ‘%X’:- It will find any values that end with “X”.
Example
mysql> Select * from Student Where name LIKE '%v'; +------+--------+---------+-----------+ | Id | Name | Address | Subject | +------+--------+---------+-----------+ | 1 | Gaurav | Delhi | Computers | | 2 | Aarav | Mumbai | History | | 20 | Gaurav | Jaipur | Computers | +------+--------+---------+-----------+ 3 rows in set (0.00 sec)
3. LIKE ‘%X%’:- It will find any values have “X” in any position.
Example
mysql> Select * from Student Where name LIKE '%h%'; +------+---------+---------+----------+ | Id | Name | Address | Subject | +------+---------+---------+----------+ | 15 | Harshit | Delhi | Commerce | | 21 | Yashraj | NULL | Math | +------+---------+---------+----------+ 2 rows in set (0.00 sec)
4. LIKE ‘X%X’:-It will find any values that start with “X” and ends with “X”.
Example
mysql> Select * from Student Where name LIKE 'a%v'; +------+-------+---------+---------+ | Id | Name | Address | Subject | +------+-------+---------+---------+ | 2 | Aarav | Mumbai | History | +------+-------+---------+---------+ 1 row in set (0.00 sec)
_ The Underscore
The underscore wildcard is used to match exactly one character. A basic syntax for using _ wildcard is as follows −Select Statement…Where column_name LIKE ‘X_’
Here, X, is any specified starting pattern such as the single character of more and _ matches exactly one character.
The underscore ‘_’ wildcard can be used, alone or in combination with %, in many ways with the specified pattern. Followings are some examples showing different LIKE operators with %. Here in these examples X represents the specified pattern.
1. LIKE ‘X_’:- It will find any values that start with “X” and having exactly one character after X.
Example
mysql> Select * from student WHERE year_of_Admission LIKE '200_'; +------+---------+---------+-----------+--------------------+ | Id | Name | Address | Subject | year_of_Admission | +------+---------+---------+-----------+--------------------+ | 1 | Gaurav | Delhi | Computers | 2001 | | 15 | Harshit | Delhi | Commerce | 2009 | | 21 | Yashraj | NULL | Math | 2000 | +------+---------+---------+-----------+--------------------+ 3 rows in set (0.00 sec)
2. LIKE ‘_X’:- It will find any values that end with “X” and having exactly one character before X.
Example
mysql> Select * from student WHERE year_of_Admission LIKE '_017'; +------+--------+---------+-----------+--------------------+ | Id | Name | Address | Subject | year_of_Admission | +------+--------+---------+-----------+--------------------+ | 20 | Gaurav | Jaipur | Computers | 2017 | +------+--------+---------+-----------+--------------------+ 1 row in set (0.00 sec)
3. LIKE _X%:- It is in combination with % wildcard. It will find any values that have X in the second position.
Example
mysql> Select * from student WHERE Name LIKE '_a%'; +------+---------+---------+-----------+--------------------+ | Id | Name | Address | Subject | year_of_Admission | +------+---------+---------+-----------+--------------------+ | 1 | Gaurav | Delhi | Computers | 2001 | | 2 | Aarav | Mumbai | History | 2010 | | 15 | Harshit | Delhi | Commerce | 2009 | | 20 | Gaurav | Jaipur | Computers | 2017 | | 21 | Yashraj | NULL | Math | 2000 | +------+---------+---------+-----------+--------------------+ 5 rows in set (0.00 sec)
4. LIKE X_%_%:- It is in combination with % wildcard. It will find any values that start with X and at least three characters in length.
Example
mysql> Select * from student WHERE Name LIKE 'g_%_%'; +------+--------+---------+-----------+--------------------+ | Id | Name | Address | Subject | year_of_Admission | +------+--------+---------+-----------+--------------------+ | 1 | Gaurav | Delhi | Computers | 2001 | | 20 | Gaurav | Jaipur | Computers | 2017 | +------+--------+---------+-----------+--------------------+ 2 rows in set (0.00 sec)
300 Views
