SQL wildcard
Wildcard characters can be used to replace any other characters in a string.
SQL Wildcards
In SQL, wildcards are used with the SQL LIKE operator.
SQL wildcards are used to search for data in a table.
In SQL, you can use the following wildcard characters:
| Wildcard character | Description |
|---|---|
| % | replaces 0 or more characters |
| _ | replaces one character |
| [charlist] | Any single character in the character list |
| [^charlist] or [ !charlist] | Any single character not in the character list |
Demo Database
In this tutorial, we will use the php sample database.
The following is the data selected from the "Websites" table:
| id | name | url --------+-------+---------+
| 1 | Google | https://www.google.cm/ | 1 | USA |
| 2 | Taobao | https://www.taobao.com/ | 13 | CN |
| 3 | php Chinese website | http://www.php.cn/ | 4689 | CN |
| 4 | Weibo | http://weibo.com/ | 20 | CN |
| 5 | Facebook | https://www.facebook.com/ | 3 | USA |
| 7 | stackoverflow | http://stackoverflow.com/ | 0 | IND |
+----+---------------+----------- ----------------+------+---------+
Use SQL % Wildcard The following SQL statement selects all websites whose URL starts with the letters "https":
Using the SQL _ wildcard character
The following SQL statement selects all customers whose name begins with an arbitrary character, followed by "oogle":
Example
WHERE name LIKE '_oogle';
Execution output result:
The following SQL statement selects all websites whose name starts with "G", then an arbitrary character, then "o", then an arbitrary character, then "le":
Example
WHERE name LIKE 'G_o_le';
Execution output result:
Use SQL [charlist] wildcard character
Use REGEXP or NOT REGEXP operator (or RLIKE and NOT RLIKE) in MySQL to operate regular expressions.
The following SQL statement selects all websites whose name starts with "G", "F" or "s":
Example
WHERE name REGEXP '^[GFs]';
Execution output result:
The following SQL statement selects name from A to H Websites starting with letters:
Example
WHERE name REGEXP '^[A-H]';
Execution output result:
The following SQL statement selects websites whose names do not start with letters A to H:
Example
WHERE name REGEXP '^[^A-H]';
Execution output result:
