What are the different wildcard characters that can be used with the NOT LIKE operator?

As we all know, NOT LIKE operator is used with WILDCARD character to not 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 NOT LIKE operator:

% - Percent

The "%" wildcard character is used to specify a pattern of 0, 1, or more characters. The basic syntax for using the % wildcard character with the NOT LIKE operator is as follows:

Select Statement…Where column_name NOT LIKE ‘X%’

where X is any specified starting pattern, e.g. A single character of more and % matches any number of characters starting from 0.

The percent '%' wildcard can be combined with the specified pattern in various ways. Here are some examples showing different NOT LIKE operators with %. In these examples, X represents the specified pattern:

1. Unlike "X%": it will look for any value that starts with "X".

Example

mysql> Select * from Student Where Name NOT LIKE 'a%';

+------+---------+---------+-----------+--------------------+
| Id   | Name    | Address | Subject   | year_of_Admission  |
+------+---------+---------+-----------+--------------------+
| 1    | Gaurav  | Delhi   | Computers |              2001  |
| 15   | Harshit | Delhi   | Commerce  |              2009  |
| 20   | Gaurav  | Jaipur  | Computers |              2017  |
| 21   | Yashraj | NULL    | Math      |              2000  |
+------+---------+---------+-----------+--------------------+

4 rows in set (0.00 sec)

2. Unlike '%X': It will look for any value divided by 'X' ending in it.

Example

mysql> Select * from Student Where Name NOT LIKE '%v';

+------+---------+---------+----------+--------------------+
| Id   | Name    | Address | Subject  | year_of_Admission  |
+------+---------+---------+----------+--------------------+
| 15   | Harshit | Delhi   | Commerce |              2009  |
| 21   | Yashraj | NULL    | Math     |              2000  |
+------+---------+---------+----------+--------------------+

2 rows in set (0.00 sec)

3. Unlike "%X%": It will look for any value except one with an "X" anywhere.

Example

mysql> Select * from Student Where Name NOT LIKE '%h%';

+------+--------+---------+-----------+--------------------+
| Id   | Name   | Address | Subject   | year_of_Admission  |
+------+--------+---------+-----------+--------------------+
| 1    | Gaurav | Delhi   | Computers |              2001  |
| 2    | Aarav  | Mumbai  | History   |              2010  |
| 20   | Gaurav | Jaipur  | Computers |              2017  |
+------+--------+---------+-----------+--------------------+

3 rows in set (0.00 sec)

4. NOT LIKE 'X%X': It will look for any value other than a value that starts with "X" and ends with "X".

Example

mysql> Select * from Student Where Name NOT LIKE 'a%v';

+------+---------+---------+-----------+--------------------+
| Id   | Name    | Address | Subject   | year_of_Admission  |
+------+---------+---------+-----------+--------------------+
| 1    | Gaurav  | Delhi   | Computers |              2001  |
| 15   | Harshit | Delhi   | Commerce  |              2009  |
| 20   | Gaurav  | Jaipur  | Computers |              2017  |
| 21   | Yashraj | NULL    | Math      |              2000  |
+------+---------+---------+-----------+--------------------+

4 rows in set (0.00 sec)

_ Underscore

The underscore wildcard is used to match exactly one character. The basic syntax for using the _ wildcard character with the NOT LIKE operator is as follows:

Select statement...Where column_name NOT LIKE 'X_'

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

The underscore "_" wildcard character can be used alone or combined with % to combine with the specified pattern in various ways. Here are some examples showing different NOT LIKE operators with %. In these examples, X represents the specified pattern:

1. Unlike "X_": It will look for any value except one that starts with "X" and is followed by only one character.

Example

mysql> Select * from Student Where Year_of_Admission NOT LIKE '200_';

+------+--------+---------+-----------+--------------------+
| Id   | Name   | Address | Subject   | year_of_admission  |
+------+--------+---------+-----------+--------------------+
| 2    | Aarav  | Mumbai  | History   |              2010  |
| 20   | Gaurav | Jaipur  | Computers |              2017  |
+------+--------+---------+-----------+--------------------+

2 rows in set (0.00 sec)

2. Unlike "_X": It will look for any value except one that ends with "X" and is preceded by just one character.

Example

mysql> Select * from Student Where Year_of_Admission NOT LIKE '_017';

+------+---------+---------+-----------+--------------------+
| Id   | Name    | Address | Subject   | year_of_admission  |
+------+---------+---------+-----------+--------------------+
| 1    | Gaurav  | Delhi   | Computers |              2001  |
| 2    | Aarav   | Mumbai  | History   |              2010  |
| 15   | Harshit | Delhi   | Commerce  |              2009  |
| 21   | Yashraj | NULL    | Math      |              2000  |
+------+---------+---------+-----------+--------------------+

4 rows in set (0.00 sec)

3. NOT LIKE _X%: It is used in conjunction with the % wildcard character. It will find any value except where there is an X in the second position.

Example

mysql> Select * from Student Where Name NOT LIKE '_a%';
Empty set (0.00 sec)

4. Unlike X_%_%: It is used in conjunction with the % wildcard character. It will look for any value that does not start with X and is at least three characters long.

Example

mysql> Select * from Student Where Name NOT LIKE 'g_%_%';

+------+---------+---------+----------+--------------------+
| Id   | Name    | Address | Subject  | year_of_Admission  |
+------+---------+---------+----------+--------------------+
| 2    | Aarav   | Mumbai  | History  |              2010  |
| 15   | Harshit | Delhi   | Commerce |              2009  |
| 21   | Yashraj | NULL    | Math     |              2000  |
+------+---------+---------+----------+--------------------+

3 rows in set (0.00 sec)

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