SQL Wildcards in Statement
A “wildcard” character is used to substitute for any other character in a string. These wildcards are used in the “LIKE” operator. Here are the 2 examples of wildcards characters :
- %: the percent symbol represents zero, one or more wildcards.
- _: the underscore symbol represents a single wildcard character.
Syntax:
SQL query to search for records where the column “column_name” begins with the letter “a”:
SELECT * FROM table WHERE column_name LIKE 'a%'
SQL query looking for records ending with the letter “a” :
[...] WHERE column_name LIKE '%a'
SQL query looking for records that have the letter “a” :
[...] WHERE column_name LIKE '%a%'
SQL query looking for records that have the letter “a” as the 2nd character:
[...] WHERE colonne LIKE '_a%'
SQL query looking for records that start with “a” and end with “z” :
[...] WHERE colonne LIKE 'a%z'
Example :
Let’s take the example of a table containing the list of products.
+------+----------+------------+-------+---------+ | id | name | category | stock | price | +------+----------+------------+-------+---------+ | 101 | RAM | computer | 6 | 850 | | 102 | Keyboard | computer | 37 | 40 | | 103 | Mouse | computer | 17 | 30 | | 104 | Pencil | fourniture | 148 | 2 | | 105 | Gum | fourniture | 250 | 1 | +------+----------+------------+-------+---------+
It is possible to search in a SQL query the list of products whose name ends with “er”, using this query:
SELECT * FROM product WHERE name LIKE '%er'
Output:
+------+----------+------------+-------+---------+ | id | name | category | stock | price | +------+----------+------------+-------+---------+ | 101 | RAM | computer | 6 | 850 | | 102 | Keyboard | computer | 37 | 40 | | 103 | Mouse | computer | 17 | 30 | +------+----------+------------+-------+---------+