Explain SQL wildcard in detail

Time:2021-1-17

Wildcards can be used to replace any other character in a string.

SQL wildcard

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 wildcards:

wildcard describe
% Replace 0 or more characters
_ Replace one character
[charlist] Any single character in a character column
[^charlist]
or
[!charlist]
Not in any of the character columns

Demo database

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

Here is the data selected from the “websites” table:

| id | name   | url      | alexa | country |
+----+--------------+---------------------------+-------+---------+
| 1 | Google  | https://www.google.cm/ | 1  | USA  |
|2. Taobao| https://www.taobao.com/  | 13 | CN  |
|3 | rookie course| http://www.runoob.com/  | 4689 | CN  |
|4 | microblog| 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 letter “HTTPS”:


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

Execution output

The following SQL statement selects all websites whose URL contains the mode “OO”:


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

Execution output:

Using SQL_ wildcard

The following SQL statement selects name to start with an arbitrary character, and then all customers of “oogle”:


SELECT * FROM Websites
WHERE name LIKE '_oogle';

Execution output

The following SQL statement selects all websites whose name starts with “g”, then an arbitrary character, then “O”, then an arbitrary character, and then “Le”:


SELECT * FROM Websites
WHERE name LIKE 'G_o_le';

Execution output

Using SQL [charlist] wildcard

MySQL uses regexp or not regexp operators (or rlike and not rlike) to manipulate regular expressions.

The following SQL statement selects all websites whose name starts with “g”, “F” or “s”:


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

Execution output:

The following SQL statement selects the website whose name starts with a to h letters:


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

Execution output:

The following SQL statement selects the website whose name does not start with a to h letters:


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

Execution output

The above is the detailed content of SQL wildcard, more information about SQL wildcard, please pay attention to other related articles of developer!