SQL Wildcard Characters With Example
1. Objective
In this SQL Tutorial, we are going to study SQL Wildcard. Also, we will see SQL Wildcard example and SQL Wildcard Characters. Moreover, we will look % Wildcard and _ Wildcard in SQL
So, let us start SQL Wildcard Tutorial.
SQL Wildcard Characters With Example
2. SQL Wildcard
The wildcard in SQL is used in a string to substitute characters. They are used just as LIKE operator, and also WHERE clause to search for a mere pattern in a column.
Do you know about SQL RDBMS Concept
SQL supports two wildcard operators with LIKE operator.
| Wildcard | Description |
| The percent sign (%) | This wildcard is used for matches in one or more characters. |
| The underscore (_) | This wildcard is used to match the character. |
Note –
- MS Access uses the asterisk (*) wildcard character instead of the percent sign (%) wildcard character.
- A question mark (?) to match any one character in MS Access.
The percent sign represents zero, one or multiple characters.
The syntax of SQL Wildcard
The syntax of a ‘%’ and a ‘_’ operator –
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_'
Let’s have a look at SQL Expressions
3. Example of SQL Wildcard
| Statement | Description |
| WHERE SALARY LIKE ‘200%’ | This wildcards finds any values that start with 200. |
| WHERE SALARY LIKE ‘%200%’ | This wildcards finds any values that have 200 in any position. |
| WHEN SALARY LIKE ‘_00%’ | This wildcards finds any values that have 00 in the second and third positions. |
| WHERE SALARY LIKE ‘2_%_%’ | This wildcards finds any values that start with 2 and are at least 3 characters in length. |
| WHERE SALARY LIKE ‘%2’ | This wildcards finds any values that end with 2. |
| WHEN SALARY LIKE ‘_2%3’ | This wildcards finds any values that have a 2 in the second position and end with a 3. |
| WHERE SALARY LIKE ‘2___3’ | This wildcards finds any values in a five-digit number and it ends with 3 and starts with 2. |
Let us take a real example of SQL Wildcard:
example of Wildcard in SQL
This code displays all the records from the CUSTOMERS table where the SALARY starts at 200.
Do you know about SQL Operators
SQL> SELECT * FROM CUSTOMERS WHERE SALARY LIKE '200%';
Output
SQL Wildcard
There are 2 wildcards in SQL utilized in conjunction with the like operator:
| % | It represents zero, one, or multiple characters |
| _ | The underscore represents one character |
Note: MS Access uses a question mark (?) rather than the underscore (_).
Below is a selection from the “Customers” table in the Northwind sample database:
Wildcard in SQL
Let’s revise the SQL Create Database
a. Using the % Wildcard
In this SQL Wildcard example, all customers with a City starting with “ber”:
Example of SQL % Wildcard
SELECT * FROM Customers WHERE City LIKE 'ber%';
In this example this statement helps to select all customers with a City containing the pattern “es”:
Example of % Wildcard in SQL
SELECT * FROM Customers WHERE City LIKE '%es%';
b. Using the _ Wildcard
In this example all customers with a City starting with any character, followed by “erlin”:
Example of _ Wildcard in SQL
SELECT * FROM Customers WHERE City LIKE '_erlin';
So, this was all in SQL Wildcard Tutorial. Hope you like our explanation.
4. Conclusion
Hence, in this SQL Wildcard tutorial, we studied about the wildcards in SQL. Moreover, we discussed SQL Wildcard Characters and example of SQL Wildcard. Still, if any doubt, ask in the comment tab.
See also –
SQL Null Values
For reference