SQL Wildcards

In this tutorial, we'll learn about the Wildcards in SQL and how to use them with examples.

A wildcard character in SQL is used with the LIKE clause to replace a single or set of characters in any string. For example,

SELECT *
FROM Customers
WHERE last_name LIKE 'R%';

Here, % (means zero or more characters) is a wildcard character. Hence, the SQL command selects customers whose last_name starts with R followed by zero or more characters after it.

What are wildcards in SQL
Example: SQL Wildcards

Recommended Reading: SQL LIKE Operator


% Wildcard in SQL

The % wildcard in SQL is used to represent zero or more characters. For example,

SELECT *
FROM Customers
WHERE last_name LIKE 'R%';

Here, the SQL command selects customers whose last name starts with R followed by zero or more characters.

Expression String Matched?
R% R match
Run match
Mere no match
Summer no match

_ Wildcard in SQL

The _ wildcard in SQL is used to represent exactly one character in a string. For example,

SELECT *
FROM Customers
WHERE country LIKE 'U_';

Here, the SQL command selects customers whose country name starts with U and is followed by only one character.

Expression String Matched?
U_ U no match
UK match
USA no match

[] Wildcard in SQL

The [] wildcard in SQL is used to represent any one character inside brackets. For example,

SELECT *
FROM Customers
WHERE country LIKE 'U[KA]%';

Here, the SQL command selects customers whose country name starts with U and is followed by either K or A. Any number of characters are allowed afterwards.

Expression String Matched?
U[KA]% U no match
UK match
UAE match
USA no match

! Wildcard in SQL

The ! wildcard in SQL is used to exclude characters from a string. For example,

SELECT *
FROM Customers
WHERE last_name LIKE '[!DR]%';

Here, the SQL command selects customers whose last_name does not start with D or R.

Expression String Matched?
[!DR]% Doe no match
Reinhardt no match
Luna match
D no match
O match
R no match

Wildcard Characters in Different Databases

Different databases have different sets of wildcard characters. Some of them are listed here.

Database Systems

SQL Server
  • % - zero or more characters
  • _ - single character
  • [] - single character within the brackets
  • ^ - any character not within the brackets
  • - - single character within a range
PostgreSQL and MySQL
  • % - zero or more characters
  • _ - single character
Oracle
  • % - zero or more characters
  • _ - means single character
  • [] - single character within the brackets
  • {} - escaped character
Did you find this article helpful?