YouTipSql Wildcards
# SQL Wildcards
* * *
Wildcards can be used to substitute any other character in a string.
* * *
## SQL Wildcards
In SQL, wildcards are used 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 |
| --- | --- |
| % | Replaces zero or more characters |
| _ | Replaces a single character |
| | 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 TUTORIAL sample database.
Here is the data selected 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 | Tutorial | | 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 the SQL % Wildcard
The following SQL statement selects all websites where the url starts with "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
SELECT * FROM Websites
WHERE url LIKE '%oo%';
Execution output:
!(#)
* * *
## Using the SQL _ Wildcard
The following SQL statement selects all customers with a name that starts with any character, followed by "oogle":
## Example
SELECT * FROM Websites
WHERE name LIKE '_oogle';
Execution output:
!(#)
The following SQL statement selects all websites with a name that starts with "G", followed by any character, then "o", then any character, then "le":
## Example
SELECT * FROM Websites
WHERE name LIKE 'G_o_le';
Execution output:
!(#)
* * *
## Using the SQL Wildcard
In SQL, the wildcard is used to specify a list of characters to match any single character from that list.
It is commonly used with the LIKE operator for pattern matching, such as in the WHERE clause.
The following SQL statement selects all websites where the name starts with "G", "F", or "s":
## Example
SELECT * FROM Websites
WHERE name REGEXP '^';
Execution output:
!(#)
The following SQL statement selects all websites where the name starts with a letter from A to H:
## Example
SELECT * FROM Websites
WHERE name REGEXP '^';
Execution output:
!(#)
The following SQL statement selects all websites where the name does not start with a letter from A to H:
## Example
SELECT * FROM Websites
WHERE name REGEXP '^[^A-H]';
Execution output:
!(#)