# The LIKE Operator in SQL: Wildcards, Performance, and Alternatives

Learn the SQL LIKE operator in depth — % and _ wildcards, case sensitivity across databases, escaping literal characters, and why leading wildcards silently disable indexes.

- Author: SqlInt team
- Published: 2026-07-07T09:00:00+00:00
- Updated: 2026-09-15T10:49:56.912877+00:00
- Canonical: https://sqlint.com/articles/the-like-operator-in-sql
- Category: Fundamentals
- Tags: like-operator, wildcards, pattern-matching, query-performance, sql-basics

---

## What the LIKE Operator Does

LIKE performs pattern-based text matching in a WHERE clause — instead of requiring an exact match like =, it lets you match text against a pattern containing wildcards. It's one of the first operators most people learn and one of the most misused in production, largely because its performance characteristics are easy to overlook until a table grows large.

## The Two Wildcards

%   -- matches zero or more of ANY character
_   -- matches exactly ONE of any character

SELECT * FROM customers WHERE name LIKE 'A%';        -- starts with 'A'
SELECT * FROM customers WHERE name LIKE '%smith';    -- ends with 'smith'
SELECT * FROM customers WHERE name LIKE '%son%';     -- contains 'son' anywhere
SELECT * FROM customers WHERE code LIKE 'A_1';        -- 'A', any one character, then '1' — e.g. 'AB1', 'AX1'
SELECT * FROM customers WHERE phone LIKE '555-___-____'; -- exact digit-count pattern with literal dashes

## Case Sensitivity Varies by Database

-- PostgreSQL: LIKE is case-SENSITIVE by default
SELECT * FROM users WHERE email LIKE '%EXAMPLE.COM';    -- won't match 'alice@example.com'

-- PostgreSQL: ILIKE is the case-INSENSITIVE variant (PostgreSQL-specific, not standard SQL)
SELECT * FROM users WHERE email ILIKE '%EXAMPLE.COM';   -- matches 'alice@example.com'

-- MySQL: LIKE is case-INSENSITIVE by default on the common case-insensitive collations
-- (behavior actually depends on the column's collation, not a fixed rule)
SELECT * FROM users WHERE email LIKE '%EXAMPLE.COM';    -- typically matches, depending on collation

-- SQL Server: also collation-dependent, case-insensitive on the default collation in most installs

Never assume case sensitivity without checking — it depends on the database and, for MySQL/SQL Server, the specific column's collation setting. When correctness matters, be explicit: wrap both sides in LOWER()/UPPER() for guaranteed case-insensitive matching regardless of database or collation, or use ILIKE on PostgreSQL specifically.

## Escaping Literal Wildcard Characters

If the text you're searching for actually contains a literal % or _ (a discount code like SAVE_10, or a percentage value stored as text), you need to escape the wildcard so it's treated as a literal character, not a pattern.

-- Standard SQL ESCAPE clause: defines a custom escape character
SELECT * FROM promo_codes WHERE code LIKE 'SAVE\_10' ESCAPE '\';
-- The \_ here means "a literal underscore", not "match any one character"

-- Without ESCAPE, this would incorrectly match SAVE_10, SAVEX10, SAVE510, etc.
SELECT * FROM promo_codes WHERE code LIKE 'SAVE_10';

## NOT LIKE

SELECT * FROM products WHERE name NOT LIKE '%discontinued%';

Same NULL caveat applies here as with any comparison — NOT LIKE against a NULL value returns UNKNOWN, not TRUE, so rows with a NULL in the matched column are silently excluded from both a LIKE and a NOT LIKE filter. See our NULL handling guide for the full reasoning behind this.

## The Performance Problem: Leading Wildcards

This is the single most important thing to understand about LIKE for any real-world or interview context. A standard B-tree index (see our indexes guide) stores values in sorted order — which makes it perfectly usable for a pattern that's anchored at the start, but useless for a pattern that starts with a wildcard.

-- CAN use a standard index on name — behaves like a range scan (>= 'Al' AND

A trailing wildcard ('Al%') is sargable — the index can be used. A leading wildcard ('%son' or '%son%') is not — the database has to check every single row, since a match could start anywhere in the string.

## What to Use Instead of a Leading-Wildcard LIKE at Scale

  - Full-text search — PostgreSQL's built-in tsvector/tsquery, MySQL's FULLTEXT indexes, or SQL Server's Full-Text Search — purpose-built for "contains this word anywhere" queries, with proper indexing support that plain LIKE can't offer.

  - Trigram indexes — PostgreSQL's pg_trgm extension can index substring/fuzzy matches, making even leading-wildcard LIKE '%son%' queries index-usable, which is otherwise impossible with a standard B-tree.

  - A dedicated search engine — Elasticsearch or similar, for search-heavy applications where relevance ranking, typo tolerance, and complex text queries matter beyond what any SQL-native option offers well.

Reach for these once a LIKE '%...%' query is running against a table large enough that the full scan is measurably slow — for small lookup tables, plain LIKE is often perfectly fine and not worth the added complexity.

## LIKE vs. Regular Expressions

-- LIKE: simple wildcards only (% and _), fast and widely portable
SELECT * FROM users WHERE email LIKE '%@gmail.com';

-- Regex: far more expressive pattern matching, but less portable across databases
SELECT * FROM users WHERE email ~ '^[a-z]+@gmail\.com$';        -- PostgreSQL
SELECT * FROM users WHERE email REGEXP '^[a-z]+@gmail\\.com$';   -- MySQL

Use plain LIKE for simple prefix/suffix/contains checks — it's faster, more portable, and more readable for that narrow purpose. Reach for regex functions (covered in our string functions guide) only when you actually need character classes, alternation, or more complex structural matching that % and _ genuinely can't express.

## SIMILAR TO: The Middle Ground (PostgreSQL/Standard SQL)

-- Standard SQL SIMILAR TO — combines LIKE-style wildcards with basic regex-like alternation
SELECT * FROM products WHERE sku SIMILAR TO '(A|B)[0-9]{3}';
-- Matches an A or B followed by exactly 3 digits

SIMILAR TO is standard SQL and supported on PostgreSQL, sitting between plain LIKE and full regex in expressiveness — less commonly used in practice than either endpoint, but worth recognizing if it appears in a question or an existing codebase.

## Common Mistakes

  - Using a leading wildcard on a large, frequently-queried table without realizing it disables index usage entirely — the most common LIKE-related performance bug in production.

  - Forgetting to escape literal % or _ characters in the search text itself, causing incorrect matches.

  - Assuming case sensitivity is consistent across databases — always verify or force it explicitly with LOWER()/UPPER() or ILIKE.

  - Reaching for LIKE '%word%' for real search functionality instead of full-text search — works at small scale, degrades badly as the table and query volume grow.

## Common Interview Questions

  - What do % and _ mean in a LIKE pattern? % matches zero or more of any character; _ matches exactly one character.

  - Why can a LIKE query with a leading wildcard be slow on a large table? A standard B-tree index is sorted and can only be seeked into from a known starting point — a pattern like '%son' could match anywhere in the string, so the index provides no help and the engine falls back to a full scan.

  - How would you search for a literal percent sign in a text column? Escape it explicitly with the ESCAPE clause, e.g. LIKE '50\%' ESCAPE '\'.

  - What would you use instead of LIKE '%keyword%' for a search feature on a large table? Full-text search (native tsvector/FULLTEXT) or a trigram index (pg_trgm) — explain why plain LIKE doesn't scale for that use case.

## Frequently Asked Questions

### Is LIKE part of standard SQL?

Yes — LIKE with % and _ wildcards is standard ANSI SQL and behaves consistently across PostgreSQL, MySQL, and SQL Server. Case sensitivity and extensions like ILIKE are where the databases diverge.

### Does adding an index always help a LIKE query?

Only for patterns anchored at the start (no leading wildcard). A standard index provides no benefit for '%contains%' or '%suffix' style patterns — you need a specialized index type (like PostgreSQL's trigram index) or full-text search for those to be genuinely fast.

## Practice Pattern Matching

LIKE is simple to learn but easy to misuse at scale — understanding exactly when it can and can't use an index is a strong, practical interview signal. Try it hands-on with our SQL practice questions, or apply it to a real search feature in our case studies. For the broader set of pattern-matching and text tools, see our string functions guide, and for the indexing concepts behind the performance discussion here, see our SQL indexes guide.

---

Source: https://sqlint.com/articles/the-like-operator-in-sql