What are SQL Wildcard Operators?

Overview

The SQL Wildcard Operators are used to substitute one or more characters in a string. Wildcard Operators are very useful for comparing two strings with the utmost precision. They are used with the SQL LIKE operator. The LIKE operator is used with the WHERE clause. They are used to search the data.

Scope

• This article deals with SQL Wildcard Operators.
• Various Wildcard Operators will be discussed in depth along with their use and examples.

Introduction

As programmers, we often need to search for data that can be further manipulated. While performing some queries on the database, you often need to retrieve information.

Assume, you need the information of those users whose name starts with A to H and the gender is ‘Male’. In the above scenario, we use Wildcard Operators to resolve complex queries.

It is similar to Regular Expressions where we search for patterns. SQL Wildcard Operators are used when we want to search in the database.

Wildcard Operators are very useful for comparing two strings with the utmost precision. They are used with the SQL LIKE operator. The LIKE operator is used with the WHERE clause. They are used to search the data.

Most common Wildcard Operators

Let us know the various wildcard operators in MS Access and SQL.

Wildcard Operators in MS Access

Wildcard Operators in SQL

As we can see, many Wildcard operators are common in MS Access and SQL. Some of them are similar in usage but have different denotations.

Need of SQL WildCard Operators

As we have seen in the introductory section, SQL wildcard operators are used to search for data. It helps programmers to search for complex data. This can be a string with alphanumeric characters.

They play a very useful role in speeding up queries. The wildcards are used to obtain quick results.

Let us see how we can use wildcard operators with LIKE.

Syntax

Let us look at the syntax using a ‘%’ and a ‘_’.

SELECT * FROM TABLE

WHERE COL_NAME LIKE ‘XXXX%’

SELECT * FROM TABLE

WHERE COL_NAME LIKE ‘%XXXX%’

SELECT * FROM TABLE

WHERE COL_NAME LIKE ‘XXXX_’

SELECT * FROM TABLE

WHERE COL_NAME LIKE ‘_XXXX’’

SELECT * FROM TABLE

WHERE COL_NAME LIKE ‘_XXXX_’

SELECT * FROM TABLE

WHERE COL_NAME LIKE ‘XXXX%’

The various conditions that we write can be combined using AND or OR conditions.

Example

In order to understand Wildcard operators in SQL, we will look into major examples.

Let us consider a dummy table. The name of the table given is ‘DETAILS’.

Now we will write queries using each wildcard operator.

1. %wildcard operator

As we know, the % operator is used to represent zero or more characters.

Example 1 -

SELECT * FROM DETAILS

WHERE Name LIKE ‘%R’

Output-

The output displays the details of those whose name starts with R.

Example 2 -

SELECT * FROM DETAILS

WHERE Name LIKE ‘I%’

Output-

The output displays the details of those whose names end with “I”.

2. _ wildcard operator

It is used to represent a single character.

Example 1 -

SELECT * FROM DETAILS

WHERE Age LIKE ‘1_’

Output-

The output displays the details of those whose age starts with 1 and ends with one character.

3. [] wildcard operator

It is used to represent any single character enclosed in the brackets.

Example 1 -

SELECT * FROM DETAILS

WHERE Name LIKE ‘De[el]pak’

Output -

The output displays the details of those whose name is either Deepak or Delpak.

4. ^ wildcard operator

It excludes the characters within brackets.

Example 1 -

SELECT * FROM DETAILS

WHERE Color LIKE ‘%[^oO]%’

Output -

The output displays the details of those whose Color doesn’t contain the letter o.

5. - wildcard operator

It is used to represent any single character that is within the range.

SELECT * FROM DETAILS

WHERE ID LIKE ‘[3–8]’

Output -

The output displays the details of those whose ID is within the range of 3 and 8.

Conclusion

• The SQL Wildcard Operators are used to substitute one or more characters in a string.
• It is similar to Regular Expressions where we search for patterns. SQL Wildcard Operators are used when we want to search in the database.
• They are used with the SQL LIKE operator. The LIKE operator is used with the WHERE clause.
• They play a very useful role in speeding up queries.
• %, _, ^, -, and [] are SQL wildcard operators.
• The condition is written using these operators to search the data.