Latest web development tutorials

SQL wildcard

Wildcards can be used in place of any other character string.


SQL wildcard

In SQL, SQL LIKE operator with the wildcard character used together.

SQL wildcard search for data in the table.

In SQL, you can use the following wildcards:

通配符 描述
% 替代 0 个或多个字符
_ 替代一个字符
[ charlist ] 字符列中的任何单一字符
[^ charlist ]

[! charlist ]
不在字符列中的任何单一字符


The demo database

In this tutorial, we will use w3big sample database.

The following is a selected "Websites" table data:

+----+--------------+---------------------------+-------+---------+
| id | name         | url                       | alexa | country |
+----+--------------+---------------------------+-------+---------+
| 1  | Google       | https://www.google.cm/    | 1     | USA     |
| 2  | 淘宝          | https://www.taobao.com/   | 13    | CN      |
| 3  | 本教程      | http://www.w3big.com/    | 4689  | CN      |
| 4  | 微博          | 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 site url letters "https" begins:

Examples

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

Execution output:

The following SQL statements contained mode select url "oo" of all sites:

Examples

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

Execution output:



Use SQL _ wildcards

The following SQL statement to select a name to start any character, then "oogle" all customers:

Examples

SELECT * FROM Websites
WHERE name LIKE '_oogle';

Execution output:

The following SQL statement select name to "G" Start, then any character, then "o", then any character, then "le" for all Web sites:

Examples

SELECT * FROM Websites
WHERE name LIKE 'G_o_le';

Execution output:



Using SQL [charlist] wildcard

MySQL use REGEXP or NOT REGEXP operators (or RLIKE and NOT RLIKE) to operate regular expressions.

The following SQL statement select name all sites to "G", "F" or "s" begins:

Examples

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

Execution output:

The following SQL statement select name beginning with A to H letters website:

Examples

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

Execution output:

The following SQL statement to select the name does not begin with the letters A to H website:

Examples

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

Execution output: