SQL Basics

SQL Functions

SQL Joins

SQL Advanced

SQL Database

SQL Tables

SQL Constraints

SQL Performance

SQL Data Types

SQL Security

SQL Wildcards

❮ Previous Next ❯

SQL Wildcard Characters

A wildcard character is used to substitute one or more characters in a string. Wildcard characters are used with the LIKE operator. The LIKE operator is used in a WHERE clause to search for a specified pattern in a column.

Common Wildcard Characters

Different database systems support different wildcard characters. Here are the most common ones:

Wildcards in MS Access

Symbol Description Example
* Represents zero or more characters bl* finds bl, black, blue, and blob
? Represents a single character h?t finds hot, hat, and hit
[] Represents any single character within the brackets h[oa]t finds hot and hat, but not hit
! Represents any character not in the brackets h[!oa]t finds hit, but not hot and hat
- Represents a range of characters c[a-b]t finds cat and cbt
# Represents any single numeric character 2## finds numbers like 200, 210, 245

Wildcards in SQL Server

Symbol Description Example
% Represents zero or more characters bl% finds bl, black, blue, and blob
_ Represents a single character h_t finds hot, hat, and hit
[] Represents any single character within the brackets h[oa]t finds hot and hat, but not hit
^ Represents any character not in the brackets h[^oa]t finds hit, but not hot and hat
- Represents a range of characters c[a-b]t finds cat and cbt

Note: For this tutorial, we will focus on the SQL Server syntax (% and _), which is also used by many other popular databases like MySQL and PostgreSQL.

Demo Database

Below is a selection from the Customers table:

Customers Table:

CustomerID CustomerName City
1 Alfreds Futterkiste Berlin
2 Ana Trujillo Emparedados México D.F.
3 Antonio Moreno Taquería México D.F.
4 Berglunds snabbköp Luleå
5 Blauer See Delikatessen Mannheim

Example 1: Using the % Wildcard

The following SQL statement selects all customers with a City starting with "ber":

`%` Wildcard Example

SELECT * FROM Customers
WHERE City LIKE 'ber%';

The following SQL statement selects all customers with a City containing the pattern "es":

`%` Wildcard Example 2

SELECT * FROM Customers
WHERE City LIKE '%es%';

Example 2: Using the _ Wildcard

The following SQL statement selects all customers with a City starting with any character, followed by "ondon".

`_` Wildcard Example

SELECT * FROM Customers
WHERE City LIKE '_ondon';
❮ Previous Next ❯