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)