SQL Wildcard Characters

SQL wildcard characters are special symbols used to represent one or more unknown characters in a string. They help in searching and filtering text data based on patterns instead of exact matches. Wildcards are mainly used with the LIKE and NOT LIKE operators to perform flexible searches.

Syntax:

SELECT column1,column2 FROM table_name
WHERE column LIKE wildcard_operator;

• column1,column2: fields in the table
• table_name: name of the table
• column: name of the field used for filtering data

Types of SQL Wildcard Characters

There are several wildcard characters in SQL, each serving a different purpose in pattern matching. Let’s break down the most common wildcard characters and their usage:

• %: Represents zero or more characters.
• _: Represents exactly one character.
• [ ]: Represents a range of characters, used to match any single character within the range.
• [ ] with ^ or !: Matches any character that is NOT in the specified range.

Example of SQL Wildcard Characters

We will use a Customers table to show how wildcard characters work in SQL searches.

1. Using the % Wildcard

The % wildcard is used to substitute for zero or more characters. It’s very flexible and is commonly used for matching partial strings.

Example 1: Records Starting with a Specific Letter

To fetch records where CustomerName starts with 'A'.

Query:

SELECT * 
FROM Customers
WHERE CustomerName LIKE 'A%';

Output:

Example 2: Records Ending with a Specific Letter

To fetch records from the Customers table with NAME ending with the letter 'I'.

Query:

SELECT * 
FROM Customers
WHERE CustomerName LIKE '%l';

Output

Example 3: Records Containing a Specific Letter at Any Position

To fetch records from the Customers table where CustomerName contains the letter 'A' at any position.

Query

SELECT * FROM Customers WHERE CustomerName LIKE '%A%';

Output:

Example 4: Records Containing a Specific Substring at Any Position

To fetch records from the Customers table where the Country contains the substring 'ra' at any position.

Query:

SELECT DISTINCT * FROM Customers WHERE Country LIKE '%ra%';

Output:

2. Using the _ Wildcard

The _ wildcard is used to substitute for exactly one character. This is useful when we know part of the string but need to match one specific unknown character.

Example 1: Records with a Specific Prefix and Exactly Three Characters

This query fetches records where CustomerName starts with "Dan" and is followed by exactly three characters.

Query:

SELECT * FROM Customers WHERE CustomerName LIKE 'Dan___';

Output:

Example 2: Records with a Specific Length

This query fetches customers whose Country name has exactly 7 characters.

Query:

SELECT * FROM Customers WHERE Country LIKE '_______';

Output:

3. Using the [ ] Wildcard

The [ ] wildcard is used to match a single character from a specified set or range of characters. It is useful for searching values that contain characters from a defined group.

Example 1: Matching One Character from a Set

This query fetches customers whose LastName starts with A, B or C.

Query

SELECT * FROM Customers WHERE LastName REGEXP '^[A-C]';

Output:

Example 2: Matching Characters Outside a Range

To fetch records from the Customers table with LastName not containing letters 'y' or 'z'.

Query

SELECT * FROM Customers WHERE LastName NOT LIKE '%[y-z]%';

Output:

4. Combining Both % and _ Wildcard

We can combine % and _ to create more accurate search patterns.% matches many characters and _ matches one, helping filter data more precisely.

Example: Matching Specific Digits in a Phone Number

This query fetches records where the phone number starts with 8, has any two characters next and 5 as the fourth character.

Query

SELECT * FROM Customers WHERE PHONE LIKE '8__5%';

Output: