Wildcards
The keyword LIKE is used with wildcards in SQL statements, with wildcards representing unknown characters. Wildcards in SQL are _
and %
. Which _
represents an unspecified character, %
represents indefinite unspecified characters.
For example, if you only remember that the first four digits of the phone number are 1101 and the last two digits are forgotten, you can replace them with two _
wildcards:
SELECT name,age,phone FROM employee WHERE phone LIKE '1101__';
and here we have phone numbers starting with 1101:
MariaDB [mysql_labex]> SELECT name,age,phone FROM employee WHERE phone LIKE '1101__';
+------+------+--------+
| name | age | phone |
+------+------+--------+
| Joe | 31 | 110129 |
| Mike | 23 | 110110 |
+------+------+--------+
2 rows in set (0.000 sec)
In another case, such as when you only remember the first letter of the name, and you do not know the length of the name, then use %
wildcard instead of indefinite characters:
SELECT name,age,phone FROM employee WHERE name LIKE 'J%';
Here we have names starting with J:
MariaDB [mysql_labex]> SELECT name,age,phone FROM employee WHERE name LIKE 'J%';
+------+------+--------+
| name | age | phone |
+------+------+--------+
| Jack | 24 | 120120 |
| Jim | 35 | 100861 |
| Joe | 31 | 110129 |
| Jobs | NULL | 19283 |
+------+------+--------+
4 rows in set (0.000 sec)