SQL - Wildcard Operators

SQL Wildcards are special temporary characters which are used as substitutes for either a single character or multiple characters. They are used with the LIKE operator in SQL, to search for specified patterns in character strings or to compare multiple various strings etc.

This is a case-sensitive operator, which means that it will match only those strings that have the same case as the specified pattern.

Following are the most commonly used wildcards in SQL −

S.No.	Wildcard & Description
1	The percent sign (%) Matches one or more characters. Note − MS Access uses the asterisk (*) wildcard character instead of the percent sign (%) wildcard character.
2	The underscore (_) Matches one character. Note − MS Access uses a question mark (?) instead of the underscore (_) to match any one character.

S.No.

Wildcard & Description

The percent sign (%)

Matches one or more characters.

Note − MS Access uses the asterisk (*) wildcard character instead of the percent sign (%) wildcard character.

The underscore (_)

Matches one character.

Note − MS Access uses a question mark (?) instead of the underscore (_) to match any one character.

The percent sign represents zero, one or multiple characters. The underscore represents a single number or a character. These symbols can be used in combinations.

Syntax

The basic syntax of a % and a _ operator is as follows.

SELECT * FROM table_name
WHERE column LIKE  XXXX% 

or 

SELECT * FROM table_name
WHERE column LIKE  %XXXX% 

or

SELECT * FROM table_name
WHERE column LIKE  XXXX_ 

or

SELECT * FROM table_name
WHERE column LIKE  _XXXX 

or

SELECT * FROM table_name
WHERE column LIKE  _XXXX_

You can combine N number of conditions using the AND or the OR operators. Here, XXXX could be any numeric or string value.

Using Wildcards in SQL

The following table demonstrates different ways of using wildcards with the LIKE operator in a WHERE clause.

S.No.	Statement & Description
1	WHERE SALARY LIKE 200% Finds any values that start with 200.
2	WHERE SALARY LIKE %200% Finds any values that have 200 in any position.
3	WHERE SALARY LIKE _00% Finds any values that have 00 in the second and third positions.
4	WHERE SALARY LIKE 2_%_% Finds any values that start with 2 and are at least 3 characters in length.
5	WHERE SALARY LIKE %2 Finds any values that end with 2.
6	WHERE SALARY LIKE _2%3 Finds any values that have a 2 in the second position and end with a 3.
7	WHERE SALARY LIKE 2___3 Finds any values in a five-digit number that start with 2 and end with 3.

Example

In the following example, consider the CUSTOMERS table having the following records.

+----+----------+-----+-----------+----------+
| ID | NAME     | AGE | ADDRESS   | SALARY   |
+----+----------+-----+-----------+----------+
|  1 | Ramesh   |  32 | Ahmedabad |  2000.00 |
|  2 | Khilan   |  25 | Delhi     |  1500.00 |
|  3 | kaushik  |  23 | Kota      |  2000.00 |
|  4 | Chaitap |  25 | Mumbai    |  6500.00 |
|  5 | Hardik   |  27 | Bhopal    |  8500.00 |
|  6 | Komal    |  22 | MP        |  4500.00 |
|  7 | Muffy    |  24 | Indore    | 10000.00 |
+----+----------+-----+-----------+----------+

The following code block would display all the records from the CUSTOMERS table where the SALARY starts with 200.

SQL> SELECT * FROM CUSTOMERS
WHERE SALARY LIKE  200% ;

Output

This would produce the following result.

+----+----------+-----+-----------+----------+
| ID | NAME     | AGE | ADDRESS   | SALARY   |
+----+----------+-----+-----------+----------+
|  1 | Ramesh   |  32 | Ahmedabad |  2000.00 |
|  3 | kaushik  |  23 | Kota      |  2000.00 |
+----+----------+-----+-----------+----------+

SQL - Wildcard Operators

Syntax

Using Wildcards in SQL

Example

Output

友情链接