SQL Like Wildcard

Introduction to SQL Like Wildcard

In SQL, a wildcard character is used as a necessary replacement for string characters. The wildcard character is used in SQL with the LIKE operator. The role of the LIKE operator is to look for a certain pattern within the column from where the search requests are being made. The use of the WHERE clause is essential when using the LIKE operator.

Example

Let’s say there is a table by the name FOOD. Now, we will use the SQL Like Wildcard character % to fetch data.

Code:

Select *
From FOOD
Where Item_Name LIKE 'B%';

As you can notice in the above SQL program, % is the wildcard character used to fetch data from the table segment Item_Name where the name of the item starts with B. You can also notice the use of the LIKE operator in the program.

Key Highlights

• SQL Wildcard characters are used for fetching values from a table
• There are two SQL Wildcard characters, “_” and “%”.
• SQL wildcard characters combine with the LIKE operator to extract values from a table under specific search queries.

SQL Like Wildcard Characters

Let’s have a look at the different wildcard characters used with the LIKE operator in SQL Server:

However, if you are using MS Access instead of SQL Server, then the characters will be different. Have a look:

SQL Like Wildcard Operators

There are two operators used in SQL Like Wildcard. These are:

1. The percent sign (%)

This operator can match a single character or multiple ones. However, MS Access represents this operator differently. In MS Access, * (asterisk) is used to represent this operator.

Example:

Consider the table Food with column name Item_Name.

Code:

Select * from FOOD
Where Item_Name LIKE 'B%'

or

Select * from FOOD
Where Item_Name LIKE '%B'

2. The underscore (_)

This operator can match only a single character. However, MS Access represents this operator differently. In MS Access, ? (Question mark) is used to represent this operator.

Code:

Select * from FOOD
Where Item_Name LIKE 'B_'

or

Select * from FOOD
Where Item_Name LIKE '_B'

SQL Like Wildcard Statement

The operators % and _ can be used with SQL Like wildcard statements to fetch or compare values from a table. Having said that, there are certain ways to achieve the same. Let’s discuss this.

Syntax:

Select from table_name
Where column_name LIKE 'string pattern'

In SQL, you will use % to match any number of characters, including zero, while _ is used to match only a single character. Let’s take an example where you would like to fetch all the items from the FOOD table that starts with the letter “B”. In that case, you need to use the % syntax. So, the whole syntax would be:

Syntax:

Select * from FOOD
Where Item_Name LIKE 'B%'

Now, let’s say you want to fetch every name from the FOOD table that contains the letters “Burger”. In that case, you need to write the syntax in a slightly different way. Have a look:

Syntax:

Select * from FOOD
Where Item_Name LIKE '%Burger%'

Enough of the % wildcard character. Now, what about the _ sign? You will use this _ wildcard operator to fetch a single character from the table. Let’s see an example:

Code:

Select * from FOOD
Where Item_Code LIKE '_1'

Here, the code will fetch all the two-digit item codes that end with ‘1’.

More Examples:

Let’s be more specific with the SQL Like Wildcard statement. We will use a more concise example to identify the process. Let’s consider a table by the name FOOD. It features columns Item_Code, Item_Name, and Item_Cost.

Now, we will use the ‘%’ wildcard with the LIKE statement to fetch results. Let’s have a look. We will use the % wildcard to fetch all “burger” items from the FOOD table. Have a look at the code:

Code:

select * from FOOD
where Item_Name LIKE 'Burger%';

The result that it will display is:

You can notice that 2 out of 4 entries contain the word “Burger”. Hence, it displays the results accordingly.

You can even find words that end with a specific character using the “%” wildcard. Let’s take an example where we want to find all food items that end with “s”. So, the code.

Code:

select * from FOOD
where Item_Name LIKE '%s';

This will display all the items that end with “s”.

Only “French Fries” from the FOOD table end with “s”. Hence, the result displays the same.

What about the ‘_’ wildcard? Well, we will observe some examples to have a better understanding.

Again, take a look at the FOOD table:

From the table, we will use wildcard _ to fetch the item codes that end with 1. Have a look at the coding:

Code:

select * from FOOD
where Item_Code LIKE '_1';

This will return the following output:

Now, if you want to find the item cost for all items that end with 0, you have to use two underscores. Since the item costs are all three digits long, hence you have to use two underscores. Let’s see an example:

Code:

select * from FOOD
where Item_Cost LIKE '__0';

This will return the following output:

Conclusion

The SQL Like wildcards is used to compare values from the SQL table. The two wildcards, % and _, can be used with the LIKE operator to fetch results from the SQL table, making the query process less time-consuming and a lot easier.

FAQs

Q1. How many primary SQL Wildcards are present?

Answer: There are two SQL wildcards: % and _.

Q2. What is the purpose of using LIKE in SQL Wildcard queries?

Answer: SQL Wildcards are used with Like to query for specific results from a table.

Q3. How many secondary SQL Wildcards are present?

Answer: There are 3 secondary wildcards: ^, [], -.

Recommended Articles

This article helps you to learn about SQL Like Wildcard. To know more about the same topic, you can refer to these articles.

1. MySQL Function
2. SQL Auto Increment
3. SQLAlchemy pip
4. SQLAlchemy Connection