SQL 20 🛢️ Pattern Matching with LIKE, ILIKE, and Wildcards

The comparison operators test exact values. The IN operator tests set membership. The LIKE operator tests a pattern. It answers the question “Does this string match this pattern?” The pattern uses wildcards — special characters that match any sequence or any single character. The LIKE operator is the tool for text search in SQL: finding names that start with a letter, emails from a domain, or product codes with a specific format.

The previous chapters covered the comparison operators, the logical operators, the range filter, and the set filter. This chapter covers the pattern filter. It is part of the WHERE clause, and it is used wherever a column must be tested against a pattern rather than an exact value. The patterns are simple but powerful, and the wildcards are the key.

Key point: The LIKE operator uses two wildcards. The % wildcard matches any sequence of characters — zero or more. The _ wildcard matches exactly one character. The pattern 'A%' matches any string that starts with A. The pattern '%son' matches any string that ends with son. The pattern '_a_' matches any three-character string whose middle character is a. The wildcards can be combined. The pattern 'A_%' matches any string that starts with A and is at least two characters long.

---

Why pattern matching matters

A query rarely searches for an exact value in a text column. It searches for a pattern. “Which customers have a first name that starts with ‘A’?” “Which emails are from the example.com domain?” “Which product codes start with ‘PRD-‘ and end with a digit?” The LIKE operator expresses all of these.

The exact-match problem. The = operator tests an exact match. first_name = 'Alice' matches only the exact string Alice. It does not match Alice (with a trailing space), alice (lowercase), or Alicia. The LIKE operator matches a pattern, not an exact value.

The case problem. The LIKE operator is case-sensitive in most databases. first_name LIKE 'a%' does not match Alice. PostgreSQL provides the ILIKE operator for case-insensitive matching. MySQL’s LIKE is case-insensitive by default for non-binary collations. SQL Server’s LIKE is case-insensitive by default. The behavior depends on the database and the collation.

The escape problem. The wildcards % and _ are special in a LIKE pattern. If the data contains a literal % or _, the pattern must escape it. The ESCAPE clause defines the escape character. The pattern '100\%%' with ESCAPE '\' matches any string that starts with 100%.

The performance problem. A LIKE pattern that starts with a wildcard cannot use an index. The pattern '%son' requires a full table scan because the database does not know where the matching strings start. The pattern 'A%' can use an index because the database knows the strings start with A. The leading wildcard is the performance trap.

The trade-off. The LIKE operator is simple and portable. It is standard SQL. But it is not the right tool for full-text search. For that, databases provide specialized features: tsvector in PostgreSQL, MATCH ... AGAINST in MySQL, CONTAINS in SQL Server. The LIKE operator is for simple pattern matching on short strings.

---

a. The LIKE Operator and Wildcards

The LIKE operator tests whether a string matches a pattern. The syntax is:

value LIKE pattern

The pattern is a string that can contain wildcards. The % wildcard matches any sequence of characters. The _ wildcard matches any single character.

The query selects customers whose first name starts with A:

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

The % matches any sequence of characters after the A. The query matches Alice, Amanda, Andrew, and A. It does not match Bob or alice.

The query selects customers whose last name ends with son:

SELECT * FROM customers WHERE last_name LIKE '%son';

The % matches any sequence of characters before the son. The query matches Johnson, Wilson, Anderson, and son.

The query selects customers whose first name contains li:

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

The two % wildcards match any characters before and after li. The query matches Alice, Felix, Olivia, and li.

The _ wildcard matches exactly one character. The query selects customers whose first name is exactly three characters long:

SELECT * FROM customers WHERE first_name LIKE '___';

Three _ wildcards match exactly three characters. The query matches Bob, Amy, and Eve. It does not match Alice or Jo.

The _ wildcard can be combined with %. The query selects customers whose first name starts with A and is at least two characters long:

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

The A matches the first character. The _ matches exactly one character. The % matches any sequence of characters. The query matches Alice, Amanda, and Andrew. It does not match A.

---

b. Case Insensitivity: ILIKE and Collations

The LIKE operator is case-sensitive in most databases. first_name LIKE 'a%' does not match Alice. The behavior depends on the database and the collation.

PostgreSQL provides the ILIKE operator for case-insensitive matching:

SELECT * FROM customers WHERE first_name ILIKE 'a%';

The ILIKE operator matches Alice, alice, ALICE, and a. It is PostgreSQL-specific.

MySQL’s LIKE is case-insensitive by default for the standard collations. The utf8mb4_0900_ai_ci collation is accent-insensitive and case-insensitive. The utf8mb4_0900_as_cs collation is accent-sensitive and case-sensitive. The behavior is determined by the column’s collation.

SQL Server’s LIKE is case-insensitive by default. The behavior is determined by the column’s collation. The Latin1_General_CI_AS collation is case-insensitive and accent-sensitive. The Latin1_General_CS_AS collation is case-sensitive.

Oracle’s LIKE is case-sensitive. The REGEXP_LIKE function provides more advanced pattern matching with case-insensitivity options.

The portable way to do case-insensitive matching is to use the LOWER() or UPPER() function:

SELECT * FROM customers WHERE LOWER(first_name) LIKE 'a%';

The LOWER() function converts the column to lowercase. The pattern is written in lowercase. The query is portable but the function on the column prevents the use of an index.

---

c. Escaping Wildcards

The % and _ characters are wildcards in a LIKE pattern. If the data contains a literal % or _, the pattern must escape it.

The ESCAPE clause defines the escape character. The escape character precedes the wildcard in the pattern to make it literal.

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

The pattern '100\%' with ESCAPE '\' matches the exact string 100%. The backslash escapes the %, so the % is treated as a literal character.

The escape character can be any character. The backslash is common, but it must be escaped in some contexts.

SELECT * FROM products WHERE discount LIKE '100!%' ESCAPE '!';

The pattern '100!%' with ESCAPE '!' matches the exact string 100%. The exclamation mark is the escape character.

Without the ESCAPE clause, the % is a wildcard. The pattern '100%' matches any string that starts with 100, including 100, 1000, 100%, and 100abc.

The _ wildcard is escaped the same way:

SELECT * FROM products WHERE code LIKE 'A\_B' ESCAPE '\';

The pattern 'A\_B' matches the exact string A_B. The backslash escapes the _, so the _ is treated as a literal character.

The ESCAPE clause is standard SQL. It is supported by PostgreSQL, MySQL, SQL Server, and Oracle.

---

Complete Example Session

This session demonstrates the LIKE operator on a small table.

-- ============================================
-- PART 1: THE TABLE
-- ============================================

CREATE TABLE customers (
    customer_id INT PRIMARY KEY,
    first_name  VARCHAR(50),
    last_name   VARCHAR(50),
    email       VARCHAR(100)
);

INSERT INTO customers VALUES
    (1, 'Alice', 'Johnson', 'alice@example.com'),
    (2, 'Bob', 'Smith', 'bob@gmail.com'),
    (3, 'Carol', 'Williams', 'carol@example.com'),
    (4, 'Amanda', 'Anderson', 'amanda@example.org'),
    (5, 'alice', 'wilson', 'alice@gmail.com'),
    (6, 'Andrew', 'Davis', 'andrew@example.com');

-- ============================================
-- PART 2: STARTS WITH
-- ============================================

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

-- Output: Alice, Amanda, Andrew
-- Case-sensitive: 'alice' is not included.

-- ============================================
-- PART 3: ENDS WITH
-- ============================================

SELECT * FROM customers WHERE last_name LIKE '%son';

-- Output: Johnson, Wilson
-- Anderson ends with 'son' but the pattern is case-sensitive.

-- ============================================
-- PART 4: CONTAINS
-- ============================================

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

-- Output: Alice, alice
-- Both contain 'li'.

-- ============================================
-- PART 5: SINGLE CHARACTER
-- ============================================

SELECT * FROM customers WHERE first_name LIKE '_o_';

-- Output: Bob
-- Exactly three characters, middle is 'o'.

-- ============================================
-- PART 6: COMBINED WILDCARDS
-- ============================================

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

-- Output: Alice, Amanda, Andrew
-- Starts with 'A' and has at least two characters.

-- ============================================
-- PART 7: CASE-INSENSITIVE
-- ============================================

SELECT * FROM customers WHERE first_name ILIKE 'a%';

-- Output: Alice, Amanda, Andrew, alice
-- ILIKE is case-insensitive (PostgreSQL).

-- Portable alternative:
SELECT * FROM customers WHERE LOWER(first_name) LIKE 'a%';

-- Output: Alice, Amanda, Andrew, alice

-- ============================================
-- PART 8: ESCAPING
-- ============================================

CREATE TABLE discounts (
    discount_id INT PRIMARY KEY,
    label VARCHAR(50)
);

INSERT INTO discounts VALUES
    (1, '100% off'),
    (2, '100 items'),
    (3, '1000');

SELECT * FROM discounts WHERE label LIKE '100\%' ESCAPE '\';

-- Output: 100% off
-- The backslash escapes the %, so the % is literal.

-- ============================================
-- PART 9: THE PERFORMANCE TRAP
-- ============================================

-- Fast: index can be used
SELECT * FROM customers WHERE first_name LIKE 'A%';

-- Slow: index cannot be used
SELECT * FROM customers WHERE first_name LIKE '%son';

-- The leading wildcard forces a full table scan.

-- ============================================
-- PART 10: THE SUMMARY
-- ============================================

-- % : any sequence of characters
-- _ : exactly one character
-- LIKE : case-sensitive (most databases)
-- ILIKE : case-insensitive (PostgreSQL)
-- ESCAPE : define the escape character
-- Leading % : performance trap

The ten parts cover the table, starts with, ends with, contains, single character, combined wildcards, case-insensitive, escaping, the performance trap, and the summary.

---

Quick Reference

The LIKE Operators

Operator	Purpose
LIKE	Pattern match
NOT LIKE	Negated pattern match
ILIKE	Case-insensitive (PostgreSQL)
NOT ILIKE	Negated case-insensitive

The Wildcards

Wildcard	Matches
%	Any sequence of characters (zero or more)
_	Exactly one character

The Pattern Examples

Pattern	Matches
'A%'	Starts with A
'%son'	Ends with son
'%li%'	Contains li
'_a_'	Three chars, middle is a
'A_%'	Starts with A, at least two chars

The Case Sensitivity

Database	Default
PostgreSQL	Case-sensitive
MySQL	Case-insensitive (standard collation)
SQL Server	Case-insensitive (default collation)
Oracle	Case-sensitive

The Escape Clause

Pattern	ESCAPE	Matches
'100\%%'	'\'	Starts with 100%
'100!%'	'!'	Starts with 100%
'A\_B'	'\'	Exact A_B

---

Best Practices

✅ Do This:

-- Use LIKE with a trailing wildcard
WHERE first_name LIKE 'A%'                                       -- ✅

-- Use ILIKE for case-insensitive matching (PostgreSQL)
WHERE first_name ILIKE 'a%'                                      -- ✅

-- Use ESCAPE for literal wildcards
WHERE label LIKE '100\%' ESCAPE '\'                              -- ✅

-- Use LOWER() for portable case-insensitivity
WHERE LOWER(first_name) LIKE 'a%'                                -- ✅

❌ Don’t Do This:

-- Don't use a leading wildcard on a large table
WHERE last_name LIKE '%son'  -- full scan                        -- ❌

-- Don't forget that LIKE is case-sensitive
WHERE first_name LIKE 'a%'  -- does not match Alice              -- ⚠️

-- Don't use LIKE for full-text search
WHERE description LIKE '%database%'  -- use full-text search     -- ❌

-- Don't forget to escape literal wildcards
WHERE label LIKE '100%'  -- matches 100, 1000, 100%              -- ⚠️

---

Common Pitfalls

Pitfall	Why It Happens	Fix
Case mismatch	LIKE is case-sensitive	Use ILIKE or LOWER()
Wildcard not literal	No ESCAPE clause	Add ESCAPE
Slow query	Leading % wildcard	Avoid or use full-text search
Index not used	Function on column	Rewrite or add a functional index
Pattern too broad	Too many % wildcards	Use more specific patterns

---

Real-World Examples

1. Starts With

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

2. Ends With

SELECT * FROM customers WHERE last_name LIKE '%son';

3. Contains

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

4. Single Character

SELECT * FROM customers WHERE first_name LIKE '_o_';

5. Combined

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

6. Case-Insensitive

SELECT * FROM customers WHERE first_name ILIKE 'a%';

7. Escaped Wildcard

SELECT * FROM discounts WHERE label LIKE '100\%' ESCAPE '\';

8. NOT LIKE

SELECT * FROM customers WHERE first_name NOT LIKE 'A%';

9. Email Domain

SELECT * FROM customers WHERE email LIKE '%@example.com';

10. Product Code

SELECT * FROM products WHERE code LIKE 'PRD-____';

---

Visual

The Wildcards

┌──────────────────────────────────────────────┐
│  % : any sequence of characters (zero or more)│
│  _ : exactly one character                   │
│                                              │
│  'A%'    → starts with A                     │
│  '%son'  → ends with son                     │
│  '%li%'  → contains li                       │
│  '_a_'   → three chars, middle is a          │
│  'A_%'   → starts with A, at least 2 chars   │
│                                              │
└──────────────────────────────────────────────┘

The Case Sensitivity

┌──────────────────────────────────────────────┐
│  PostgreSQL: LIKE is case-sensitive          │
│    'a%' does not match 'Alice'               │
│    Use ILIKE for case-insensitive            │
│                                              │
│  MySQL: LIKE is case-insensitive by default  │
│    'a%' matches 'Alice' and 'alice'          │
│                                              │
│  SQL Server: LIKE is case-insensitive        │
│    (depends on collation)                    │
│                                              │
└──────────────────────────────────────────────┘

The Escape

┌──────────────────────────────────────────────┐
│  ESCAPE CLAUSE                               │
│                                              │
│  WHERE label LIKE '100\%' ESCAPE '\'         │
│    └─ Matches the exact string '100%'        │
│    └─ The \ escapes the %                    │
│                                              │
│  WHERE label LIKE '100%'                     │
│    └─ Matches '100', '1000', '100%'          │
│    └─ The % is a wildcard                    │
│                                              │
└──────────────────────────────────────────────┘

The Performance Trap

┌──────────────────────────────────────────────┐
│  PERFORMANCE                                 │
│                                              │
│  LIKE 'A%'                                   │
│    └─ Index can be used                      │
│    └─ Fast                                   │
│                                              │
│  LIKE '%son'                                 │
│    └─ Index cannot be used                   │
│    └─ Full table scan                        │
│    └─ Slow on large tables                   │
│                                              │
└──────────────────────────────────────────────┘

---

Summary

Item	Value
LIKE	Pattern match
NOT LIKE	Negated pattern match
%	Any sequence of characters
_	Exactly one character
ILIKE	Case-insensitive (PostgreSQL)
ESCAPE	Define the escape character
Case-sensitive	PostgreSQL, Oracle
Case-insensitive	MySQL, SQL Server (default)
Leading %	Performance trap
Index usage	Only with a non-leading wildcard

Key takeaways:

• The LIKE operator tests whether a string matches a pattern. The pattern uses wildcards: % matches any sequence of characters, and _ matches exactly one character. The pattern 'A%' matches any string that starts with A .
• The LIKE operator is case-sensitive in most databases. PostgreSQL and Oracle treat LIKE as case-sensitive. MySQL and SQL Server treat it as case-insensitive by default (depending on the collation). PostgreSQL provides the ILIKE operator for case-insensitive matching. The portable way is to use LOWER() or UPPER() .
• The wildcards can be combined. The pattern 'A_%' matches any string that starts with A and is at least two characters long. The A matches the first character, the _ matches exactly one character, and the % matches any sequence after that .
• The ESCAPE clause defines an escape character. The escape character precedes a wildcard to make it literal. The pattern '100\%' with ESCAPE '\' matches the exact string 100%. Without the escape, the % is a wildcard .
• A leading wildcard prevents the use of an index. The pattern '%son' cannot use an index because the database does not know where the matching strings start. The pattern 'A%' can use an index because the strings start with A. The leading wildcard is the performance trap .
• The LIKE operator is not full-text search. It is for simple pattern matching on short strings. For full-text search, databases provide specialized features: tsvector in PostgreSQL, MATCH ... AGAINST in MySQL, CONTAINS in SQL Server .
• The NOT LIKE operator is the negation. It matches strings that do not match the pattern. It uses the same wildcards and the same ESCAPE clause. It is case-sensitive or case-insensitive depending on the database.

Remember: The LIKE operator tests a pattern. The % wildcard matches any sequence of characters. The _ wildcard matches exactly one character. The operator is case-sensitive in PostgreSQL and Oracle, and case-insensitive in MySQL and SQL Server by default. The ILIKE operator provides case-insensitive matching in PostgreSQL. The ESCAPE clause handles literal wildcards. The leading wildcard prevents index usage. The LIKE operator is the tool for the pattern, and the pattern is the wildcard.

---

Stop using slow, ad-bloated tool sites! 🤮

🔎 Search “KandZ Tools” on Google to use many professional utilities for free.

KandZ.me is the ultimate minimalist hub for:
✅ Finance (Mortgage, Interest, Inflation)
✅ Tech (Base64, JSON, Dev Suite, IP)
✅ Health (BMI, BMR, TDEE)
✅ Productivity (Timer, Workspace, QR)

⚡️ Fast & Private
🔒 No data leaves your device
💎 100% Free

🔗 Use it now: https://tools.kandz.me
🔖 Bookmark it—you’ll need it later!