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

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:

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

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:

SQL Wildcard

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

Leave a Reply

Your email address will not be published. Required fields are marked *

This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.