Wildcards in SQL

What are Wildcards in SQL?

A wildcard is a character or a group of characters that can be used to replace any other character or characters in a value defined by its constraints. In SQL, the LIKE operator uses wildcard characters. The LIKE operator is used to look for a certain pattern in a column when used with the WHERE clause.

Why Do We Need Wildcards in SQL?

Imagine that you are tasked with finding the salaries of all people whose names start with A. Alternatively, imagine you want to find the salaries of people whose name starts and ends with A. All the data is stored in SQL in the form of tables in a database. Such a task would be difficult using simple SELECT queries. That's where wildcards enter. SQL Wildcards are used to search for complex data and are handy in speeding up the querying process.

How Many Wildcards are There in SQL?

In SQL, wildcards are used with the LIKE operator. There are 4 wildcards in SQL. They are as follows:

Examples of Wildcards in SQL

To understand the concept of wildcards in detail, we will consider a table DETAILS that will be used for the examples below.

1. %Description: The percentage sign (%) is used to match zero or more characters. The characters can be either digits, alphabets or special characters.

Syntax:

Example 1: Display details of those whose names start in A.

Output:

Example 2: Display details of those whose name end with an A.

Output:

Example 3: Display details of those whose name contain an A.

Output:

2. _ Description: The underscore (_) is used to denote a single character. The characters can be either digits, alphabets or special characters.

Syntax:

Example 1: Colour containing 3 characters and ending with 'ed'

Output:

Example 2: Colour containing 5 characters and starting with 'Wh'

Output:

3. [list_of_char] Description: Matches a character with a character in the list of characters that are surrounded by square brackets.

Syntax:

Example 1: Details of one whose location contains at least one vowel.

Output:

Example 2: Details of one whose location contains ends in any letter between a to e.

Output:

4. [!list_of_char] or [^list_of_char] Description: Matches when the character is not equal to any of the characters mentioned in the list. The characters can be either digits, alphabets or special characters.

Syntax:

Example 1: Details of one whose location does not end in the letter E.

Output:

Example 2: Details of one whose location does not start with the letter S.

Output:

Learn more about advanced concepts of SQL

To learn more about some interesting and advanced concepts in SQL, check out our SQL Reading List on Scaler Topics.