SQL guide · Filtering

SQL LIKE: Match Text Patterns With Wildcards

The LIKE operator compares a text value against a pattern. Two wildcards give it flexibility: % matches any sequence of characters, including none, and _ matches exactly one character. Everything else in the pattern must match literally.

It is the standard tool for prefix, suffix and contains searches, such as finding names that start with a letter or emails from one domain. Add NOT to find values that do not fit the pattern. This guide also covers what older material calls the SQL LIKE query or SQL-LIKE syntax; they are the same operator.

Pattern position determines speed. A prefix pattern like 'Sm%' can use an ordinary index on the column, but a leading wildcard like '%son' usually forces a scan of every row. For heavy text search, a full-text index or a trigram index is the better fit.

Basic syntax

SELECT column1
FROM table_name
WHERE column LIKE 'pattern';

WHERE column NOT LIKE 'pattern'
WHERE column LIKE 'pattern' ESCAPE '\'

% stands for zero or more characters and _ for exactly one. To search for a literal percent or underscore, define an escape character with ESCAPE and place it before the wildcard. Patterns are text, so numeric columns are implicitly converted in some databases.

SQL LIKE examples

Starts with, ends with, contains

The position of the percent sign sets the meaning. The three queries find names beginning with A, email addresses ending with a domain, and product names containing a word.

SELECT * FROM customers WHERE name LIKE 'A%';
SELECT * FROM customers WHERE email LIKE '%@example.com';
SELECT * FROM products  WHERE name LIKE '%wireless%';

Single character wildcard

Each underscore stands for one character. This matches five-character codes that start with AB and end with 9, such as AB129 or ABX89 but not AB19.

SELECT product_id, sku
FROM products
WHERE sku LIKE 'AB__9';

NOT LIKE

Exclude rows that match a pattern. This removes internal test accounts whose email starts with test.

SELECT customer_id, email
FROM customers
WHERE email NOT LIKE 'test%';

Matching a literal percent sign

To find a real percent character, declare an escape character and place it before the wildcard. This finds discount descriptions that contain the text 20%.

SELECT promo_id, description
FROM promotions
WHERE description LIKE '%20!%%' ESCAPE '!';

Where SQL dialects differ

PostgreSQL: LIKE is case sensitive. Use ILIKE for case-insensitive matching, ~ for regular expressions, and a pg_trgm index to speed up %contains% searches.

MySQL: Case sensitivity follows the column collation, which is usually case insensitive. Use LIKE BINARY to force a case-sensitive match. The default escape character is a backslash.

SQL Server: Case follows the collation. Square brackets work as character classes in patterns, for example LIKE '[A-C]%' or '[^0-9]%', which standard SQL lacks.

SQLite: LIKE is case insensitive for ASCII characters by default. Use GLOB for case-sensitive matching with Unix-style wildcards * and ?.

Oracle: Case sensitive by default. Use UPPER(col) LIKE or REGEXP_LIKE for flexible matching. There is no default escape character.

Common mistakes

  • Starting a pattern with % on a large table and wondering why it is slow. A leading wildcard normally prevents index use.
  • Using = with wildcards, as in name = 'A%'. Wildcards only work with LIKE.
  • Forgetting that _ is a wildcard, so searching for snake_case also matches snakeXcase. Escape the underscore.
  • Assuming case rules are the same everywhere. A query that matches in MySQL may return nothing in PostgreSQL.
  • Not trimming trailing spaces in fixed-width CHAR columns, so a pattern without a trailing % fails to match.

Related SQL guides

Browse all SQL guides

Need help writing the SQL?

Describe your tables and the result you want, then review the generated query against your database before running it.

Try the AI SQL generator