SQL Wildcards
Wildcards can be used to substitute any other character(s) in a string.
SQL Wildcards
In SQL, wildcards are used in conjunction with the SQL LIKE operator.
SQL wildcards are used to search for data within a table.
In SQL, the following wildcards can be used:
| Wildcard | Description |
|---|---|
| % | Substitutes for zero or more characters |
| _ | Substitutes for a single character |
| [charlist] | Any single character in the list |
| [^charlist] <br>or <br>[!charlist] | Any single character not in the list |
Demo Database
In this tutorial, we will use the tutorialpro sample database.
Below is the data from the "Websites" table:
mysql> SELECT * FROM Websites;
+----+---------------+---------------------------+-------+---------+
| id | name | url | alexa | country |
+----+---------------+---------------------------+-------+---------+
| 1 | Google | https://www.google.cm/ | 1 | USA |
| 2 | Taobao | https://www.taobao.com/ | 13 | CN |
| 3 | tutorialpro.org | http://www.tutorialpro.org/ | 5000 | USA |
| 4 | Weibo | http://weibo.com/ | 20 | CN |
| 5 | Facebook | https://www.facebook.com/ | 3 | USA |
| 7 | stackoverflow | http://stackoverflow.com/ | 0 | IND |
+----+---------------+---------------------------+-------+---------+
Using SQL % Wildcard
The following SQL statement selects all websites where the url starts with the letter "https":
Example
SELECT * FROM Websites
WHERE url LIKE 'https%';
Execution output:
The following SQL statement selects all websites where the url contains the pattern "oo":
Example
Execution output:
Using SQL _ Wildcard
The following SQL statement selects all websites where the name starts with any single character, followed by "oogle":
Example
SELECT * FROM Websites
WHERE name LIKE '_oogle';
Execution output:
The following SQL statement selects all websites where the name starts with "G", followed by any single character, then "o", followed by any single character, then "le":
Example
SELECT * FROM Websites
WHERE name LIKE 'G_o_le';
Execution output:
Using SQL [charlist] Wildcard
In MySQL, REGEXP or NOT REGEXP operators (or RLIKE and NOT RLIKE) are used to work with regular expressions.
The following SQL statement selects all websites where the name starts with "G", "F", or "s":
Example
SELECT * FROM Websites
WHERE name REGEXP '^[GFs]';
Execution output:
The following SQL statement selects all websites where the name starts with a letter from A to H:
Example
Execution output:
The following SQL statement selects all websites where the name does not start with a letter from A to H:
Example
Execution output: