What different wildcard characters can be used with the MySQL LIKE operator?

As we all know, LIKE operator is used with WILDCARD character to get a string with specified string. Basically, wildcards are characters that help search for data that matches complex criteria. The following are the wildcard types that can be used with the LIKE operator

% -Percent

The "%" wildcard is used to specify 0, 1, or more pattern characters. The basic syntax for using the % wildcard is as follows

Select Statement…Where column_name LIKE ‘X%’

Here, X is a single character for any specified starting pattern, such as more, and % matches any number of characters starting from 0.

The percent "%" wildcard can be combined with a specified pattern in a variety of ways. Here are some examples showing the different LIKE operators with %. In these examples, X represents the specified mode.

1. LIKE 'X%':- It will find any value starting 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 value ending 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 value with an “X” anywhere.

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 look for items starting with "X" and ending with "X" all values.

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)

_ Underscore

The underline wildcard is used to match exactly one character. The basic syntax for using the _ wildcard is as follows: -Select statement...Where column_name LIKE 'X_'

Here, X is a single character of any specified starting pattern, such as more, and _ matches exactly one character.

The underscore "_" wildcard can be used alone or in combination with % to be used with specified patterns in a variety of ways. Here are some examples showing the different LIKE operators with %. In these examples, X represents the specified mode.

1. LIKE 'X_': - It will find all values ​​starting with "X" and only 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 items ending with "X" and preceded by X exactly one character any value.

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%:- Used in conjunction with the % wildcard character. It will find all 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_%_%:- Used in conjunction with the % wildcard character. It will find any value that starts with X and is at least three characters long.

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)

The above is the detailed content of What different wildcard characters can be used with the MySQL LIKE operator?. For more information, please follow other related articles on the PHP Chinese website!