VMware ESXi and vSphere Cluster Management

SQL LIKE Operator: Pattern Matching with Wildcards

Learn how to use SQL LIKE with percent and underscore wildcards for prefix, suffix, contains, and fixed-length text searches.

What the SQL LIKE operator does

LIKE is a SQL comparison operator for checking whether a string expression conforms to a text pattern. It is commonly used in a WHERE clause to filter text columns such as names, email addresses, product codes, and descriptions.

Unlike =, which normally tests for an exact value, LIKE can use wildcard characters to match text whose content or length varies.

-- Exact equality: only the value Mary matches
SELECT firstName
FROM Employees
WHERE firstName = 'Mary';

-- Pattern matching: Mary and other names beginning with Mar can match
SELECT firstName
FROM Employees
WHERE firstName LIKE 'Mar%';

A wildcard is a special pattern character that stands for one or more unknown characters. SQL commonly provides two LIKE wildcards: % and _.

Basic LIKE syntax

SELECT column_name
FROM table_name
WHERE column_name LIKE 'pattern';

• SELECT specifies the columns to return.
• FROM specifies the table.
• WHERE filters rows according to a condition.
• column_name is the text column being tested.
• LIKE performs the pattern comparison.
• 'pattern' is a quoted string containing ordinary characters and, optionally, wildcards.

Use SELECT * when you genuinely need every column. Selecting only the columns needed by an application or report is usually clearer and can reduce the amount of data returned.

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

The percent wildcard (%)

The percent sign, %, matches a sequence of zero, one, or many characters. Because it can match an empty sequence, a pattern such as 'M%' matches both the exact value M and longer values beginning with M.

Prefix searches

A prefix search finds values beginning with known text. Put the percent wildcard at the end of the pattern.

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

This returns first names beginning with M, such as Mary, Mami, and Martin.

Suffix searches

A suffix search finds values ending with known text. Put the percent wildcard at the beginning.

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

This can match surnames such as Johnson, Wilson, and Anderson, depending on the rows in the table.

Contains searches

A contains search finds known text anywhere in a value. Put a percent wildcard on both sides.

SELECT employeeNumber, firstName
FROM Employees
WHERE firstName LIKE '%err%';

The pattern matches any first name containing the character sequence err, whether that sequence occurs at the beginning, middle, or end.

Employee-table examples

Assume an Employees table with these columns:

First names beginning with M

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

Other employee patterns

-- Last names ending in son
SELECT employeeNumber, lastName, firstName
FROM Employees
WHERE lastName LIKE '%son';

-- First names containing err
SELECT employeeNumber, firstName
FROM Employees
WHERE firstName LIKE '%err%';

Underscore wildcard (_)

The underscore, _, matches exactly one character. It differs from %, which matches any number of characters, including none.

SELECT code
FROM Products
WHERE code LIKE 'A____';

The pattern A____ requires five characters in total: an A followed by exactly four characters. For example, A1234 matches, while A123 and AB12345 do not.

-- Exactly three characters, with B as the middle character
SELECT code
FROM Products
WHERE code LIKE '_B_';

Wildcard placement and result meaning

SQL evaluates the LIKE condition separately for each row. A row is returned when the value in the tested column conforms to the pattern and the condition is true.

A pattern without wildcards, such as LIKE 'Mary', behaves similarly to exact matching in many SQL systems. For an exact comparison, however, = 'Mary' communicates the intention more clearly.

Case sensitivity and SQL dialects

Whether LIKE treats uppercase and lowercase letters as equal depends on the database product, the column's data type, and the configured collation. A collation is a set of database rules that affects text comparison, including sorting and sometimes case sensitivity.

Do not assume that LIKE 'm%' will match names beginning with uppercase M. Check the documentation and collation settings for the target DBMS.

PostgreSQL provides ILIKE for case-insensitive pattern matching:

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

ILIKE is PostgreSQL-specific. Other database systems may use a collation, a function, or another dialect-specific feature instead.

Searching for literal wildcard characters

Sometimes the text itself contains a percent sign or underscore, such as a discount description or a code. In a LIKE pattern, those characters normally act as wildcards. To search for them literally, use an escape character and declare it with ESCAPE.

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

Here, ! is the chosen escape character. The sequence !% means “a literal percent sign,” while the first and last percent signs remain wildcards. Escape syntax and default escaping behavior can vary by database, so verify the exact rules for your DBMS.

Use the same strategy for a literal underscore, for example a pattern containing !_ when ! is declared as the escape character.

LIKE and NULL values

NULL means that a value is absent or unknown. A LIKE comparison against NULL does not evaluate to true, so rows with NULL text values are not returned by an ordinary LIKE filter.

-- Only non-NULL first names beginning with M are returned
SELECT employeeNumber, firstName
FROM Employees
WHERE firstName LIKE 'M%';

-- Include NULL first names explicitly
SELECT employeeNumber, firstName
FROM Employees
WHERE firstName LIKE 'M%'
   OR firstName IS NULL;

-- Find rows whose first name is present but does not begin with M
SELECT employeeNumber, firstName
FROM Employees
WHERE firstName NOT LIKE 'M%'
  AND firstName IS NOT NULL;

Use IS NULL and IS NOT NULL when the treatment of missing values matters to the result.

Performance considerations

Pattern placement can affect performance, especially on large tables. A prefix pattern such as 'Mar%' may be able to use an ordinary index on the column, depending on the DBMS, collation, and query plan.

A contains search such as '%mar%' begins with a wildcard. It often cannot use a normal index as efficiently and may require examining many rows. Performance depends on the database engine, indexes, data distribution, collation, and configuration.

• Prefer a prefix search when the requirement allows it.
• Return only the columns needed by the query.
• Inspect the execution plan for slow searches.
• For frequent contains searches, investigate database-specific text-search features or indexes.

Troubleshooting LIKE queries

Key points

• LIKE compares a text expression with a quoted pattern in a condition, usually inside WHERE.
• % matches zero or more characters.
• _ matches exactly one character.
• Use text% for a prefix search, %text for a suffix search, and %text% for a contains search.
• Case sensitivity, escaping rules, and performance vary by database system and collation.
• LIKE does not match NULL values unless NULL is handled explicitly with IS NULL.

For related filtering concepts, see SQL LIKE Operator.