DbSchema Tutorial | SQL Wildcard Characters

Publish on DbSchema Blog >>>

SQL (Structured Query Language) is a powerful language for managing and querying relational databases. One of its strengths is the ability to filter records based on specific patterns rather than exact matches. This is where SQL wildcard characters come into play.

In this guide, we’ll delve deep into SQL wildcard characters, showcasing their functionality, common use cases, and much more.

What are SQL Wildcards?

In SQL, a wildcard character is a special symbol that represents one or more characters. Wildcards are used in conjunction with the LIKE operator in SQL queries to search for a specified pattern in a column.

SQL Wildcard Characters

Wildcard	Description
%	Represents zero or more characters.
_	Represents a single character.
[]	Represents any single character in range.
!	Represents any character not in range.
-	Used to define a range.

% Wildcard in SQL

The % wildcard is used to represent zero, one, or multiple characters.

Syntax:

SELECT column_name(s)
FROM table_name
WHERE column_name LIKE pattern;

Example:

Consider a sample table Students:

Name
Alice
Alina
Bob
Charlie

Query:

SELECT Name
FROM Students
WHERE Name LIKE 'Ali%';

Result:

Name
Alice
Alina

Explanation:

The query retrieves names that start with Ali followed by zero or more characters.

_ Wildcard in SQL

The _ wildcard is used to represent a single character.

Example:

Query:

SELECT Name
FROM Students
WHERE Name LIKE '_ob';

Result:

Name
Bob

Explanation:

The query retrieves names with any single character followed by ob.

[] Wildcard in SQL

The [] wildcard is used to specify a range of characters.

Example:

Query:

SELECT Name
FROM Students
WHERE Name LIKE '[A-C]%';

Result:

Name
Alice
Alina
Charlie

Explanation:

This fetches names beginning with any character between A and C.

! Wildcard in SQL

The ! wildcard, when used inside [], negates the character range.

Example:

Query:

SELECT Name
FROM Students
WHERE Name LIKE '[!A-C]%';

Result:

Name
Bob

Explanation:

This fetches names NOT beginning with characters between A and C.

Using the - Wildcard

The - wildcard, when used inside [], specifies a range.

Example:

Query:

SELECT Name
FROM Students
WHERE Name LIKE '[A-B]%';

Result:

Name
Alice
Alina
Bob

Explanation:

Matches names starting with any character between A and B, inclusive.

Combining Wildcards

You can combine wildcards to form complex patterns.

Example:

Query:

SELECT Name
FROM Students
WHERE Name LIKE 'A_i%';

Result:

Name
Alice
Alina

Explanation:

Matches names beginning with A, followed by any character, and then i.

SQL Wildcard with the LIKE Operator

Wildcards are primarily used with the LIKE operator. As shown in the examples above, the LIKE operator followed by a pattern that includes wildcards can filter out records based on specific criteria.

Using SQL Wildcards Without the LIKE Operator

It’s rare, but you can use wildcards with operators other than LIKE. However, in such cases, they are treated as regular characters and not wildcards.

Inverting SQL Wildcard Filters

To fetch records that don’t match a certain pattern, use NOT LIKE.

Example:

Query:

SELECT Name
FROM Students
WHERE Name NOT LIKE 'A%';

Result:

Name
Bob
Charlie

Escaping Wildcard Characters

If you want to search for a string that includes a wildcard character as a regular character, you need to escape it.

Example:

Assume a table with names that include %.

Sample Table:

Consider this table, where some names contain the % character:

Name
Alice%Smith
Bob
Charlie%Jones
David
Erin%Taylor

Query:

SELECT Name
FROM Students
WHERE Name LIKE '%\%%' ESCAPE '\';

Result:

Name
Alice%Smith
Charlie%Jones
Erin%Taylor

Explanation:

The query searches for names in the Students table that contain the % character. Using the ESCAPE clause allows us to treat % as a literal character in the pattern, rather than its typical wildcard functionality. As a result, names like Alice%Smith, Charlie%Jones, and Erin%Taylor are retrieved.

Common Mistakes:

1. Case Sensitivity: Depending on the database system, SQL wildcards can be case-sensitive. Ensure you’re aware of the system’s behavior.
2. Overuse: Overusing wildcards, especially at the start of patterns, can slow down queries.

FAQs:

1. Can I use wildcards in the SELECT statement?

   • No, wildcards in SQL are primarily for filtering records, not for selecting columns.

2. Is there a performance difference between _ and %?

   • Yes, % might be slower, especially if used at the beginning of a pattern since it matches many more possibilities.

3. Can I combine multiple wildcards?

   • Yes, wildcards can be combined in any manner to create complex patterns.

Practice Questions:

1. Write a SQL query to find names ending with ie.
2. How can you fetch names that have o as the second character?
3. Retrieve names that don’t start with vowels.
4. How would you search for names that have _ in them?

Conclusion:

SQL wildcard characters offer a powerful way to filter records based on patterns. Understanding and mastering them can greatly enhance your querying capabilities. Remember to use them wisely to ensure optimal query performance.

Happy Querying !!