VMware ESXi and vSphere Cluster Management

SQL Wildcards and the LIKE Operator

Learn how SQL wildcards work with LIKE to find text by prefix, suffix, substring, character position, and fixed-length patterns.

SQL wildcards let you search for text when you know only part of a value. Instead of requiring an exact match, you create a pattern containing literal characters and wildcard characters. The LIKE operator evaluates that pattern in a WHERE clause.

This lesson uses a sample customers table with first_name, city, and country columns.

Exact comparison versus pattern comparison

The = operator compares a column with one exact value:

SELECT *
FROM customers
WHERE country = 'Germany';

This returns rows whose country value is exactly Germany. It does not search for values that merely begin with, end with, or contain that text.

The LIKE operator compares a text value with a pattern:

SELECT *
FROM customers
WHERE country LIKE 'Ger%';

The pattern matches values beginning with Ger, such as Germany. A wildcard is a pattern character that represents unknown text.

LIKE operator syntax

The basic form is:

SELECT column_list
FROM table_name
WHERE text_column LIKE 'pattern';

The pattern is normally a quoted string literal. It can contain ordinary characters, the percent wildcard (%), and the single-character wildcard (_). LIKE returns rows whose column values match the supplied pattern.

SQL wildcard reference

The percent wildcard

The percent sign (%) matches a sequence containing zero, one, or many characters. It can appear at the beginning, end, middle, or multiple times in a pattern.

Find values beginning with text

SELECT *
FROM customers
WHERE country LIKE 'Ger%';

This is a prefix search. It matches countries beginning with Ger, such as Germany. Because % can match zero characters, the pattern could also match the exact value Ger if that value exists.

Find values ending with text

SELECT *
FROM customers
WHERE city LIKE '%burg';

This is a suffix search. It matches cities ending in burg, such as Hamburg or Gothenburg.

Find text anywhere in a value

SELECT *
FROM customers
WHERE city LIKE '%an%';

The percent signs allow characters before and after an. The query can match cities such as London, Manchester, or San Francisco, depending on the data.

Use percent signs in multiple positions

SELECT *
FROM customers
WHERE city LIKE 'San%co%';

This pattern requires San first, then later requires co. Each percent sign can represent zero or more characters.

The single-character wildcard

The underscore (_) matches exactly one character. Unlike %, it cannot match zero characters or a sequence of multiple characters.

Match a fixed length

SELECT *
FROM customers
WHERE country LIKE 'U__';

This pattern has three positions: U, one unknown character, and another unknown character. It matches three-character values beginning with U, such as USA or UAE. It does not match UK because that value has only two characters, or Uganda because it has more than three.

Match a character at a specific position

SELECT *
FROM customers
WHERE first_name LIKE 'J_n';

This matches three-character first names that begin with J and end with n, such as Jan or Jon. The underscore represents exactly the middle character.

SELECT *
FROM customers
WHERE city LIKE '_YC';

This matches a three-character city value whose second and third characters are YC, such as NYC. It does not match NY or LYC1.

Common LIKE pattern forms

Worked customer-table examples

Country prefix

SELECT *
FROM customers
WHERE country LIKE 'Ger%';

If the table contains Germany, that row matches because its value starts with Ger. A value such as France does not match. The query also permits the value to contain additional characters after the prefix.

City suffix

SELECT city
FROM customers
WHERE city LIKE '%burg';

This returns cities whose final four characters are burg, for example Hamburg. A city containing burg only in the middle would not match this suffix pattern.

City substring

SELECT city
FROM customers
WHERE city LIKE '%an%';

This returns cities containing the consecutive letters an anywhere. The letters may occur at the beginning, middle, or end.

First-name position matching

SELECT first_name
FROM customers
WHERE first_name LIKE 'J_n';

This returns three-letter names such as Jan and Jon. It does not return John, because J_n has exactly three character positions.

Combining wildcards

SELECT *
FROM customers
WHERE first_name LIKE 'M__%';

This requires a first name beginning with M, followed by at least two more characters. The final percent sign allows additional characters, so the pattern can match names of three or more characters beginning with M.

Searching for literal percent signs and underscores

In a LIKE pattern, % and _ have special meanings. If the data contains those symbols as ordinary characters, use an escape character to remove their wildcard meaning.

The ESCAPE clause designates one character as the escape character. In this portable-style example, a backslash makes the underscore literal:

SELECT *
FROM products
WHERE product_code LIKE 'A\_%' ESCAPE '\';

Here, \_ means a literal underscore. The final % remains a wildcard, so this can match values such as A_100 and A_CODE.

To search for a literal percent sign, escape it similarly:

SELECT *
FROM products
WHERE description LIKE '%100\%%' ESCAPE '\';

This searches for text containing the literal sequence 100%. Exact escaping rules, string-literal handling, and default escape behavior can vary between database systems, so check the documentation for the database in use.

Matching behavior and limitations

NULL values

NULL represents a missing or unknown value. It is not ordinary text, so a LIKE condition typically does not match it.

SELECT *
FROM customers
WHERE city IS NULL;

Use IS NULL when you need rows with missing city values. To include missing cities along with a pattern match, combine conditions explicitly:

SELECT *
FROM customers
WHERE city LIKE '%an%'
   OR city IS NULL;

Case sensitivity and collation

Whether LIKE treats uppercase and lowercase letters as equal depends on the database system, the column or database collation, and sometimes the operator. A collation is a set of database rules for comparing and ordering text.

For example, one database configuration might treat ger% and Germany as a match, while another might require matching letter case. Some systems provide ILIKE for case-insensitive pattern matching:

SELECT *
FROM customers
WHERE country ILIKE 'ger%';

ILIKE is database-specific and is not available in every SQL implementation. When portability matters, check the database's collation rules or deliberately normalize the values and search text with functions supported by that system.

LIKE is not a regular expression engine

LIKE has a small wildcard vocabulary: primarily % for zero or more characters and _ for exactly one character. Its patterns are not regular expressions. Regular-expression operators and functions, where available, use different syntax and provide more advanced features.

Performance considerations

A prefix pattern such as 'Ger%' may be more index-friendly because the database can often identify a starting range of values. A pattern beginning with a wildcard, such as '%an%', usually gives the database less information about where matching values begin and may require a broader scan.

• Prefer a prefix search when it accurately represents the requirement.
• Expect leading-wildcard searches such as LIKE '%text%' to be more expensive on large tables.
• Use the database's query-plan tools to verify performance rather than assuming an index will be used.
• For advanced searching across large text fields, investigate database-appropriate full-text search features.

LIKE behavior and database considerations

Troubleshooting LIKE queries

A search for 100% returns too many rows

The percent sign was interpreted as a wildcard instead of ordinary text. Escape it and use an ESCAPE clause supported by the target database.

An underscore pattern matches unexpected values

In a LIKE pattern, underscore means any one character. Escape the underscore when the data must contain a literal underscore, for example LIKE 'A\_%' ESCAPE '\'.

Rows with missing values are absent

Missing values are NULL, not text that can match a pattern. Use IS NULL, or combine IS NULL with the intended LIKE condition.

Letter casing behaves differently across databases

Check the database's collation and case-sensitivity rules. Use a database-specific case-insensitive operator such as ILIKE where available, or normalize case consistently before comparison.

A large substring search is slow

A leading wildcard can prevent efficient use of ordinary prefix-oriented indexes. If the requirement permits, change the search to a prefix pattern, inspect the execution plan, or consider full-text search.

Quick reference

-- Exact comparison
SELECT * FROM customers WHERE country = 'Germany';

-- Starts with Ger
SELECT * FROM customers WHERE country LIKE 'Ger%';

-- Ends with burg
SELECT * FROM customers WHERE city LIKE '%burg';

-- Contains an
SELECT * FROM customers WHERE city LIKE '%an%';

-- Three characters beginning with U
SELECT * FROM customers WHERE country LIKE 'U__';

-- Three-letter name: J, any character, n
SELECT * FROM customers WHERE first_name LIKE 'J_n';

-- Missing city
SELECT * FROM customers WHERE city IS NULL;

-- Literal underscore, followed by any text
SELECT * FROM products
WHERE product_code LIKE 'A\_%' ESCAPE '\';

For a concise introduction to this topic, see SQL Wildcards.