SQL Wildcards

Wildcards can be used to replace 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 in a table.

In SQL, the following wildcards can be used:

Demo Database

In this tutorial, we will use the EXAMPLE sample database.

The following is data selected from the "Websites" table:

mysql> SELECT * FROM Websites;
+----+---------------+---------------------------+-------+---------+
| id | name          | url                       | alexa | country |
+----+---------------+---------------------------+-------+---------+
|  1 | Google        | https://www.google.cm/    |     1 | USA     |
|  2 | 淘宝          | https://www.taobao.com/   |    13 | CN      |
|  3 | Example       | http://www.example.com/    |  5000 | USA     |
|  4 | 微博           | 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 whose url starts with the letters "https":

Example

SELECT * FROM Websites
WHERE url LIKE 'https%';

Execute output result:

The following SQL statement selects all websites whose url contains the pattern "oo":

Example

SELECT * FROM Websites
WHERE url LIKE '%oo%';

Execute output result:

Using the SQL _ Wildcard

The following SQL statement selects all customers whose name starts with any single character and is then followed by "oogle":

Example

SELECT * FROM Websites
WHERE name LIKE '_oogle';

Execute output result:

The following SQL statement selects all websites whose name starts with "G", then any single character, then "o", then any single character, then "le":

Example

SELECT * FROM Websites
WHERE name LIKE 'G_o_le';

Execute output result:

Using the SQL [charlist] Wildcard

In SQL, the [charlist] wildcard is used to specify a list of characters, used to match any single character in that list.

It is usually used with the LIKE operator for pattern matching, such as in the WHERE clause.

The following SQL statement selects all websites whose name starts with "G", "F", or "s":

Example

SELECT * FROM Websites
WHERE name REGEXP '^[GFs]';

Execute output result:

The following SQL statement selects websites whose name starts with letters A through H:

Example

SELECT * FROM Websites
WHERE name REGEXP '^[A-H]';

Execute output result:

The following SQL statement selects websites whose name does not start with letters A through H:

Example

SELECT * FROM Websites
WHERE name REGEXP '^[^A-H]';

Execute output result: