SQL online course

SQL Wildcards and the LIKE Operator

Learn how SQL LIKE patterns use % and _ wildcards for prefix, suffix, substring, and fixed-format text searches.

SQL wildcards let you search text by a pattern instead of requiring an exact value. They are commonly used with the LIKE operator in a WHERE clause.

A wildcard is a special pattern character that represents one or more unknown characters. A pattern is a quoted string containing literal text, wildcard characters, or both.

What SQL wildcards are used for

Wildcards are useful when you need to find values that:

• Begin with a character or substring, such as countries beginning with Ger.
• End with a character or substring, such as last names ending in son.
• Contain text anywhere, such as cities containing an.
• Match a fixed character format, such as a three-character name ending in an.

LIKE in a WHERE clause

The basic syntax is:

SELECT column_list
FROM table_name
WHERE character_column LIKE 'pattern';

LIKE compares a character column with a quoted pattern. The pattern can contain ordinary characters and the wildcard characters % and _.

This differs from exact equality. The following query finds only rows whose country value is exactly Germany:

SELECT *
FROM Customers
WHERE Country = 'Germany';

A pattern search can find several related values:

SELECT *
FROM Customers
WHERE Country LIKE 'Ger%';

The comparison operator = checks for an exact value. LIKE checks whether the value fits a pattern.

LIKE wildcard characters

The percent wildcard (%)

The percent wildcard, %, matches any sequence of zero or more characters. It can appear at the beginning, end, or both sides of literal text.

Prefix searches

A prefix search finds values that start with specified text. Put % after the text:

SELECT *
FROM Customers
WHERE Country LIKE 'Ger%';

This can match Germany and any other country value beginning with Ger. The literal characters Ger must appear at the start; the percent wildcard matches whatever follows.

Suffix searches

A suffix search finds values that end with specified text. Put % before the text:

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

This matches cities ending in burg.

For names ending in son:

SELECT LastName, FirstName
FROM Customers
WHERE LastName LIKE '%son';

Substring searches

A substring search finds text anywhere in a value. Put % on both sides:

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

This matches a city with an at the beginning, middle, or end.

For example, to find cities containing Haven:

SELECT LastName, FirstName, City
FROM Customers
WHERE City LIKE '%Haven%';

This can return New Haven, because Haven occurs within the complete city value.

Zero characters are allowed

Because % matches zero or more characters, 'A%' can match both Alan and the value A itself. The wildcard does not require at least one character after A.

The underscore wildcard (_)

The underscore wildcard, _, matches exactly one character. It is useful when a value must follow a fixed format.

This query searches for three-character first names ending in an:

SELECT LastName, FirstName
FROM Customers
WHERE FirstName LIKE '_an';

The first underscore represents exactly one character, so values such as Dan and Jan can match. A four-character value such as Alan does not match this exact pattern.

Compare the wildcards:

• % matches zero, one, or many characters.
• _ matches exactly one character.

A fixed partial format can combine literal characters and an underscore:

SELECT LastName, FirstName
FROM Customers
WHERE LastName LIKE 'Br_wn';

The pattern can match Brown, where _ represents the single character between Br and wn.

Combining wildcards and literal text

Ordinary characters in a pattern must occur in their corresponding positions. Wildcards control only the portions they represent.

Multiple wildcards can appear in one pattern:

SELECT LastName, FirstName
FROM Customers
WHERE LastName LIKE 'Sm_th%';

Here, _ represents one character after Sm, while the final % represents zero or more characters after th.

Worked examples with a Customers table

Assume a Customers table with columns such as FirstName, LastName, City, and Country.

Countries beginning with Ger

SELECT CustomerName, Country
FROM Customers
WHERE Country LIKE 'Ger%';

Every matching country starts with the literal prefix Ger. Therefore, records whose country is Germany match. A country that does not begin with those three characters does not match.

Cities beginning with San

SELECT LastName, FirstName, City
FROM Customers
WHERE City LIKE 'San%';

This can match San Francisco and similar values such as San Rafael. Because % can represent the remainder of the value, it can also match the value San exactly.

Names and cities with other patterns

SELECT LastName, FirstName
FROM Customers
WHERE LastName LIKE '%son';

This finds last names ending in son. For example, a name such as Johnson can match.

SELECT LastName, FirstName
FROM Customers
WHERE LastName LIKE 'Br_wn';

This can find Brown.

SELECT LastName, FirstName, City
FROM Customers
WHERE City LIKE '%Haven%';

This can find New Haven, because the requested text appears within the city name.

SELECT LastName, FirstName, City
FROM Customers
WHERE City LIKE '%NYC%';

This searches for NYC anywhere in the city value. The same pattern approach can be used to locate customer records with names such as King, Young, or Brown when those strings are part of a larger search requirement.

Case sensitivity and database differences

Case sensitivity for LIKE is not universal. It depends on the database system, the column or database collation, and configured comparison rules. A collation is a set of database comparison rules that can affect case and character matching.

If a search for 'ger%' does not find Germany, the comparison may be case-sensitive. Check the rules for your database before relying on a particular result.

PostgreSQL provides ILIKE for case-insensitive pattern matching:

SELECT *
FROM Customers
WHERE Country ILIKE 'ger%';

Other systems may use a case-insensitive collation or normalize both sides explicitly:

SELECT *
FROM Customers
WHERE LOWER(Country) LIKE LOWER('GER%');

Applying a function such as LOWER to a column can affect index use, so evaluate the approach for your database and workload.

Searching for literal percent signs and underscores

Inside a LIKE pattern, % and _ have special meanings. To search for an actual percent sign or underscore, declare an escape character with ESCAPE.

This query searches for product codes containing a literal underscore:

SELECT code
FROM Products
WHERE code LIKE '%!_%' ESCAPE '!';

The exclamation mark is the escape character. The sequence !_ means that the underscore is literal rather than a one-character wildcard.

The same technique can search for a literal percent sign:

SELECT code
FROM Products
WHERE code LIKE '%!%%' ESCAPE '!';

Exact escaping and support can vary by database, so consult the SQL dialect documentation when patterns also contain quote or backslash escaping.

NULL behavior

LIKE does not match NULL. A comparison involving NULL evaluates to unknown rather than true, so a row with a missing city or country is not returned by a normal LIKE condition.

To find missing values explicitly, use IS NULL:

SELECT *
FROM Customers
WHERE City IS NULL;

To exclude missing values explicitly, use IS NOT NULL:

SELECT *
FROM Customers
WHERE City IS NOT NULL
  AND City LIKE 'San%';

The IS NOT NULL condition is often useful when documenting that only present text values should be considered.

NOT LIKE

Use NOT LIKE when you want values that do not fit a pattern:

SELECT LastName, FirstName
FROM Customers
WHERE LastName NOT LIKE '%son';

Remember that rows with NULL last names are not returned by this condition either. Add an IS NULL condition if missing names should be included.

Performance considerations

Pattern matching can become expensive on large tables. A prefix search such as Country LIKE 'Ger%' can often use an ordinary index more effectively because the beginning of the value is known.

A contains search such as City LIKE '%an%' begins with a wildcard. Standard indexes often cannot narrow the search efficiently from the start, so the query may need to inspect many rows.

• Prefer a selective prefix search when it meets the requirement.
• Inspect the execution plan for important queries.
• Evaluate suitable indexes for the database system.
• Consider database-specific full-text or specialized text-search features for large-scale substring searches.
• Test case-normalization expressions such as LOWER(column), because they may change index usage.

Common troubleshooting problems

Quick reference

• LIKE 'Ger%': starts with Ger.
• LIKE '%burg': ends with burg.
• LIKE '%an%': contains an.
• LIKE '_an': exactly three characters ending in an.
• LIKE 'Br_wn': Br, exactly one character, then wn.
• LIKE 'pattern' ESCAPE '!': declares ! as the escape character.
• NOT LIKE: excludes values matching a pattern.