SQL online course

SQL LIKE Operator: Pattern Matching with Wildcards

Learn how to use SQL LIKE with % and _ wildcards for prefix, suffix, substring, and fixed-position text searches, including NULLs, escaping, and performance.

The SQL LIKE operator tests whether a character value matches a pattern. Unlike the equality operator (=), which requires an exact value, LIKE lets you describe unknown characters with wildcards.

It is commonly used to search names, product codes, email addresses, descriptions, and other character columns. A pattern is the text supplied to LIKE; it can contain literal characters and wildcard characters.

A wildcard is a special pattern character that represents one or more unknown characters. The two standard LIKE wildcards are % and _.

Basic LIKE Syntax

LIKE normally appears in a WHERE clause, which filters rows according to a condition.

SELECT column_name
FROM table_name
WHERE column_name LIKE 'pattern';
  • Selected columns: the values returned by SELECT.
  • Source table: the table named after FROM.
  • Searched column: the character column after WHERE.
  • LIKE: the pattern-matching predicate.
  • Pattern: the quoted text that contains literal characters and, optionally, wildcards.

SELECT * is convenient for demonstrations because it returns every column, but explicitly selecting the columns you need usually makes a query clearer and reduces unnecessary data.

-- Exact comparison: the value must be exactly 'Maria'
SELECT employeeNumber, firstName
FROM employees
WHERE firstName = 'Maria';

-- Pattern comparison: the value must begin with 'Mar'
SELECT employeeNumber, firstName
FROM employees
WHERE firstName LIKE 'Mar%';

The Percent Wildcard (%)

The percent wildcard, %, represents zero or more characters. Its position determines which part of the value is fixed.

Prefix matches: values that start with text

Put % after the literal text to find values beginning with that text.

SELECT employeeNumber, lastName, firstName
FROM employees
WHERE firstName LIKE 'M%';

This matches first names such as Maria, Michael, and M. The last example demonstrates that % may represent zero characters, so a one-character value can match when the column permits it.

Suffix matches: values that end with text

Put % before the literal text to allow any preceding characters.

SELECT employeeNumber, lastName, firstName
FROM employees
WHERE lastName LIKE '%son';

This can match Johnson, Jackson, and Wilson, because each value ends in son.

Substring matches: text anywhere in a value

Put % on both sides of the text to search for a substring, meaning a sequence of characters occurring anywhere within the value.

SELECT employeeNumber, lastName, firstName
FROM employees
WHERE firstName LIKE '%ar%';

This can match Maria, Carla, and Barbara. It does not require ar to be at the beginning or end.

Worked Employees Examples

Assume an employees table with these columns:

  • employeeNumber — the employee identifier
  • lastName — the family name
  • firstName — the given name

For example, the table might contain the following rows:

  • 1001, Martin, Maria
  • 1002, Miller, Michael
  • 1003, Johnson, Carla
  • 1004, Jackson, David
  • 1005, Brown, Barbara
  • 1006, Smith, Henry

To find first names beginning with M:

SELECT employeeNumber, lastName, firstName
FROM employees
WHERE firstName LIKE 'M%';

The matching rows are Maria and Michael. Carla, David, Barbara, and Henry do not begin with M.

To find last names ending in son:

SELECT employeeNumber, lastName, firstName
FROM employees
WHERE lastName LIKE '%son';

Johnson matches because the pattern allows any characters before the final son. Martin, Miller, Brown, and Smith do not match this suffix.

To find names containing ar anywhere:

SELECT employeeNumber, lastName, firstName
FROM employees
WHERE firstName LIKE '%ar%';

Maria and Barbara match because they contain the sequence ar. Michael and Henry do not.

The Single-Character Wildcard (_)

The underscore wildcard, _, represents exactly one character. Unlike %, it cannot represent zero characters or multiple characters.

SELECT employeeNumber, lastName, firstName
FROM employees
WHERE firstName LIKE 'M_ry';

The pattern has four positions: literal M, one unknown character, literal r, and literal y. It matches Mary, but not Mry because a character is missing, and not Marry because there are too many characters.

Use underscores when the number and position of unknown characters matter:

-- Exactly one unknown character between A and 7
WHERE code LIKE 'A_7'

-- Exactly two unknown characters after AB
WHERE code LIKE 'AB__'

How to Read a LIKE Pattern

Read a pattern from left to right. Literal characters must occur in the stated order; wildcard characters describe what may occur around or between them.

  • M%: starts with literal M, followed by zero or more characters.
  • %son: ends with literal son, with zero or more characters before it.
  • %ar%: contains literal ar anywhere.
  • M_ry: starts with M, has exactly one unknown character, then ends with ry.

Moving a wildcard changes the meaning. For example, 'Ann%' means starts with Ann, while '%Ann' means ends with Ann, and '%Ann%' means contains Ann.

Wildcard Pattern Reference

  • literal%: starts with the literal text. North matches North%; South does not.
  • %literal: ends with the literal text. Johnson matches %son; Smith does not.
  • %literal%: contains the literal text. Barbara matches %ar%; Michael does not.
  • literal_literal: has exactly one character between the two literal parts. AB7CD matches AB_C D only when the pattern is written with the intended positions; always count each character carefully.
  • Escaped wildcard: treats a percent sign or underscore as ordinary text instead of a wildcard.

Case Sensitivity and Collation

Whether LIKE is case-sensitive is not universal. It depends on the database product, the column or database collation, and configuration. A collation is a set of comparison rules that can affect case handling and sorting.

Check your SQL system before assuming that 'm%' and 'M%' behave identically. PostgreSQL provides ILIKE for case-insensitive pattern matching:

SELECT employeeNumber, firstName
FROM employees
WHERE firstName ILIKE 'm%';

Other systems may use a case-insensitive collation, a function such as LOWER(), or another dialect-specific feature. Applying functions to a searched column can affect index use, so verify the behavior and query plan for your database.

Matching Literal Percent Signs and Underscores

Because % and _ are wildcards, a search for an actual percent sign or underscore must escape that character. The ESCAPE clause declares a character that gives the following wildcard its literal meaning.

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

Here, ! is the escape character, so !_ means a literal underscore. To search for a literal percent sign with the same escape character, use !%.

Escape syntax and default behavior can vary among SQL systems. Consult the documentation for the database in use, especially when patterns are assembled by application code.

NULL Values and LIKE

NULL represents a missing or unknown value. Applying LIKE to NULL does not produce true; it produces an unknown result. A WHERE clause returns only rows whose condition is true, so NULL text values are excluded.

-- Returns names beginning with M, but not NULL names
SELECT employeeNumber, firstName
FROM employees
WHERE firstName LIKE 'M%';

-- Includes both the pattern matches and missing names
SELECT employeeNumber, firstName
FROM employees
WHERE firstName LIKE 'M%'
   OR firstName IS NULL;

Use IS NULL or IS NOT NULL for NULL tests; do not use = NULL.

Performance Considerations

A pattern beginning with a literal prefix, such as 'Mar%', may be more index-friendly than a pattern beginning with %. Database systems differ, and collation, indexes, data distribution, and query planning all matter.

Contains searches such as '%term%' can be expensive on large tables because an ordinary index often cannot efficiently identify where the match begins. Before optimizing, inspect the query plan and measure the query on representative data.

  • Use a fixed starting prefix when the requirement allows it.
  • Keep the searched column free of unnecessary functions when index use matters.
  • For broad text searching, evaluate database-specific full-text search, trigram indexes, or other specialized indexing features.
  • Do not assume that an optimization for one SQL product applies to another.

LIKE Behavior and Common Caveats

  • Case sensitivity: depends on the database, collation, and configuration. Verify the active rules or use a dialect-specific alternative.
  • NULL values: do not satisfy a LIKE condition. Add IS NULL when missing values should be included.
  • Literal wildcard characters: require an escape character and an appropriate ESCAPE clause.
  • Leading wildcards: patterns such as %term% may require more work on large tables than prefix patterns.
  • Unexpected spaces or characters: can prevent an apparent match. Inspect stored values and normalize or trim data only when appropriate.

Troubleshooting LIKE Queries

No rows are returned for an apparently matching value

  • Check whether the wildcard is in the correct position: prefix, suffix, or both sides.
  • Check case-sensitivity rules for the database and active collation.
  • Inspect the stored value for trailing spaces, different punctuation, or unexpected characters.
  • Confirm that the value is not NULL.

A search for % or _ matches too many rows

The symbol is probably being interpreted as a wildcard. Choose an escape character and use ESCAPE according to your database dialect.

NULL names are missing from the results

This is expected because LIKE NULL is unknown rather than true. Add a separate condition such as firstName IS NULL when those rows belong in the result.

A contains search is slow

A leading percent wildcard can limit ordinary index use. Try a literal prefix if possible, review the execution plan, or investigate a database-specific full-text or specialized text index.

Exam-Ready Summary

  • LIKE compares a character value with a pattern; = compares exact values.
  • % means zero or more characters.
  • _ means exactly one character.
  • 'M%' is a prefix match, '%son' is a suffix match, and '%ar%' is a substring match.
  • Case behavior depends on the SQL product, collation, and configuration.
  • Escape literal percent signs and underscores with an appropriate escape character.
  • Use IS NULL separately when NULL values must be included.
  • Leading-wildcard searches may be expensive on large tables.