Database Management SystemsWHERE Clause

The WHERE Clause: Filtering Data with Precision

LevelBeginner

Duration55 mins

TopicWHERE Clause

5 / 5

LIKE Patterns: Flexible String Matching

Beyond Exact Matches

Exact equality isn't always what you need. Find products containing 'wireless'. List customers whose names start with 'John'. Show emails from any '.edu' domain. These queries require pattern matching—the ability to specify what a string should look like rather than what it should exactly be.

The LIKE operator is SQL's built-in pattern matching tool, using wildcard characters to specify flexible match criteria. While simple in concept, LIKE has nuances in syntax, performance, and behavior that separate casual users from experts.

What You Will Learn

By the end of this page, you will master LIKE's wildcard syntax, understand when patterns can (and cannot) use indexes, handle special characters with escape sequences, and know when to reach for more powerful pattern matching tools like SIMILAR TO or regular expressions.

LIKE Syntax and Wildcards

LIKE compares a string value against a pattern containing special wildcard characters. The basic syntax is:

column LIKE 'pattern'

Two Wildcard Characters:

LIKE Wildcard Characters
Wildcard	Meaning	Matches Examples
`%` (percent)	Zero or more characters	`'%test%'` matches 'test', 'testing', 'a test case', 'atestb'
`_` (underscore)	Exactly one character	`'_est'` matches 'test', 'best', 'rest'; NOT 'est' or 'atest'

like_basics.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
-- Prefix matching: Starts with
SELECT * FROM products WHERE name LIKE 'iPhone%';
-- Matches: iPhone, iPhone 15, iPhone 15 Pro Max
 
-- Suffix matching: Ends with
SELECT * FROM users WHERE email LIKE '%@gmail.com';
-- Matches: john@gmail.com, test.user@gmail.com
 
-- Contains: Anywhere in string
SELECT * FROM articles WHERE title LIKE '%database%';
-- Matches: Database Design, The database layer, building databases
 
-- Single character wildcard
SELECT * FROM products WHERE sku LIKE 'A_C';
-- Matches: ABC, A1C, A-C
-- Does NOT match: AC, ABBC, A12C
 
-- Combining wildcards
SELECT * FROM users WHERE phone LIKE '+1-___-___-____';
-- Matches: +1-555-123-4567 format
 
SELECT * FROM products WHERE code LIKE 'PRD_____';
-- Products with code starting 'PRD' followed by exactly 5 characters

Pattern Placement:

Common LIKE Patterns
Pattern	Description	Use Case
`'prefix%'`	Starts with 'prefix'	Autocomplete, prefix search
`'%suffix'`	Ends with 'suffix'	File extensions, domain matching
`'%contains%'`	Contains 'contains' anywhere	Keyword search, filtering
`'exact'`	Exact match (no wildcards)	Equivalent to = but slower
`'a_b'`	Three chars: a, any, b	Format validation
`'%\_test%'`	Contains literal underscore	Escape sequence usage

NOT LIKE: Pattern Exclusion

NOT LIKE finds strings that do NOT match a pattern. It's the logical negation of LIKE.

Basic Usage:

not_like.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
-- Exclude specific patterns
SELECT * FROM users WHERE email NOT LIKE '%@spam.com';
-- Users not from spam.com
 
SELECT * FROM products WHERE name NOT LIKE 'OBSOLETE%';
-- Products not marked obsolete
 
SELECT * FROM logs WHERE message NOT LIKE '%DEBUG%';
-- Non-debug log entries
 
-- Combine with other conditions
SELECT * FROM customers 
WHERE name NOT LIKE 'Test%' 
  AND name NOT LIKE '%DELETE%'
  AND email NOT LIKE '%@example.com';
-- Exclude test data patterns

NOT LIKE and NULL

Like all comparison operators, NOT LIKE returns UNKNOWN when applied to NULL. If name is NULL, name NOT LIKE '%test%' is UNKNOWN (not TRUE), so the row is excluded. Handle NULLs explicitly if needed: name NOT LIKE '%test%' OR name IS NULL.

not_like_null.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
-- NOT LIKE excludes NULLs silently
SELECT * FROM products WHERE description NOT LIKE '%special%';
-- Excludes: products containing 'special'
-- Also excludes: products where description IS NULL (often unintended)
 
-- Include NULLs when they should pass
SELECT * FROM products 
WHERE description NOT LIKE '%discontinued%' OR description IS NULL;
-- Products not marked discontinued, including those with no description
 
-- Alternative with COALESCE
SELECT * FROM products 
WHERE COALESCE(description, '') NOT LIKE '%discontinued%';
-- Treats NULL as empty string

Case Sensitivity in Pattern Matching

LIKE's case sensitivity depends on the column's collation—the rules that determine how strings are compared. This varies by database and configuration.

Database-Specific Behavior:

Default LIKE Case Sensitivity by Database
Database	Default Behavior	Case-Insensitive Option
PostgreSQL	Case-Sensitive	Use `ILIKE` operator
MySQL (default)	Case-Insensitive	Use `BINARY` keyword or binary collation
SQL Server	Depends on collation	Case-insensitive collations are common
Oracle	Case-Sensitive	Use `UPPER()` or `LOWER()`
SQLite	Case-Insensitive for ASCII	Only for ASCII letters by default

case_sensitivity.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
-- PostgreSQL: LIKE is case-sensitive
SELECT * FROM products WHERE name LIKE '%iphone%';
-- Matches: 'iphone', 'My iphone'
-- Does NOT match: 'iPhone', 'IPHONE'
 
-- PostgreSQL: ILIKE for case-insensitive
SELECT * FROM products WHERE name ILIKE '%iphone%';
-- Matches: 'iPhone', 'iphone', 'IPHONE', 'IpHoNe'
 
-- MySQL: LIKE is case-insensitive by default
SELECT * FROM products WHERE name LIKE '%iphone%';
-- Matches: 'iPhone', 'iphone', 'IPHONE' (all)
 
-- MySQL: Force case-sensitive with BINARY
SELECT * FROM products WHERE name LIKE BINARY '%iPhone%';
-- Only matches exact case
 
-- Portable case-insensitive pattern
SELECT * FROM products WHERE LOWER(name) LIKE LOWER('%iPhone%');
-- Works everywhere but may not use indexes
 
-- Better performance: Normalize data on insert
-- Store name_lower as a computed/generated column
-- CREATE INDEX idx_name_lower ON products(name_lower);
SELECT * FROM products WHERE name_lower LIKE 'iphone%';

Collation Affects Wildcards Too

With accent-insensitive collations, 'café' might match 'cafe'. With case-insensitive collations, 'ABC' matches 'abc'. Know your collation settings to predict LIKE behavior accurately.

Escape Sequences for Literal Wildcards

What if you need to search for a literal % or _ character? Since these are wildcards, they need to be escaped when you want to match them literally.

The ESCAPE Clause:

escape_basic.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
-- Problem: Find codes containing underscore
SELECT * FROM products WHERE code LIKE '%_%';
-- This matches EVERYTHING (underscore matches any single character)
 
-- Solution: Use ESCAPE clause
SELECT * FROM products WHERE code LIKE '%\_%' ESCAPE '\';
-- The backslash escapes the underscore
-- Matches: 'A_B', 'CODE_123', 'TEST_ITEM'
 
-- Escape the percent sign
SELECT * FROM discounts WHERE description LIKE '%50\%%' ESCAPE '\';
-- Matches: '50% off', 'Save 50%!'
 
-- Choose any escape character
SELECT * FROM codes WHERE code LIKE '%|_%' ESCAPE '|';
-- Using | as escape character
 
SELECT * FROM patterns WHERE pattern LIKE '%!%%' ESCAPE '!';
-- Using ! as escape character

Escaping the Escape Character:

If you need to match the escape character itself, double it:

escape_advanced.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
-- Match literal backslash
SELECT * FROM paths WHERE path LIKE '%\\%' ESCAPE '\';
-- Matches paths containing backslash
-- Note: Some databases may require additional escaping for string literals
 
-- Match backslash followed by underscore
SELECT * FROM data WHERE text LIKE '%\\_char%' ESCAPE '\';
-- Matches: 'has\_char_here'
 
-- Database-specific escape handling
-- PostgreSQL: Standard ESCAPE clause works
SELECT * FROM products WHERE name LIKE '%_%' ESCAPE '';
 
-- SQL Server: Square brackets can escape
SELECT * FROM products WHERE name LIKE '%[_]%';
-- [_] matches literal underscore
SELECT * FROM products WHERE name LIKE '%[%]%';
-- [%] matches literal percent
 
-- MySQL: Default escape is backslash
SELECT * FROM products WHERE name LIKE '%\_%';
-- Backslash escape is default, ESCAPE clause optional

User Input and LIKE Injection

When building LIKE patterns from user input, escape wildcard characters to prevent unintended matching. If a user searches for '50%', you need to escape the percent sign or they'll match far more than intended. Use parameterized patterns or escape functions provided by your database driver.

user_input_escape.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
-- Building safe LIKE patterns from user input
-- Pseudocode for application layer:
 
-- user_input = "50% off"
-- escaped_input = user_input.replace('%', '\%').replace('_', '\_')
-- query = "SELECT * FROM products WHERE description LIKE '%' + escaped_input + '%' ESCAPE '\'"
 
-- Example in PostgreSQL with parameter
PREPARE find_text(text) AS
  SELECT * FROM products 
  WHERE description LIKE '%' || replace(replace($1, '%', '\%'), '_', '\_') || '%' ESCAPE '\';
 
EXECUTE find_text('50% off');
-- Correctly searches for literal "50% off"

LIKE Performance and Index Usage

LIKE's performance depends critically on where the wildcard appears. This determines whether indexes can be used.

The Prefix Rule:

A B-tree index can only be used when the pattern has a fixed prefix (no leading wildcard).

LIKE Pattern Indexability
Pattern	Index Usable?	Reason
`'prefix%'`	✅ Yes	Fixed prefix enables index range scan
`'%suffix'`	❌ No	Leading wildcard requires full scan
`'%contains%'`	❌ No	Leading wildcard requires full scan
`'pre%fix'`	Partial	Uses index for 'pre', filters rest
`'exact'`	✅ Yes	Equivalent to = (equality)
`'_BC%'`	❌ No	Leading _ is still a leading wildcard

like_index_usage.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
-- Create index for these examples
CREATE INDEX idx_products_name ON products(name);
 
-- INDEXABLE: Prefix pattern
EXPLAIN SELECT * FROM products WHERE name LIKE 'iPhone%';
-- Uses: Index range scan on idx_products_name
-- Execution: Seek to 'iPhone', scan while name < 'iPhonf'
 
-- NOT INDEXABLE: Suffix pattern
EXPLAIN SELECT * FROM products WHERE name LIKE '%Pro';
-- Uses: Sequential scan (table scan)
-- Must examine every row
 
-- NOT INDEXABLE: Contains pattern
EXPLAIN SELECT * FROM products WHERE name LIKE '%wireless%';
-- Uses: Sequential scan
-- No way to use index for arbitrary substring
 
-- PARTIAL USE: Pattern with fixed prefix
EXPLAIN SELECT * FROM products WHERE name LIKE 'Apple%Watch%';
-- Uses: Index range scan for 'Apple%'
-- Then filters: remaining rows for '%Watch%'
 
-- Verify with EXPLAIN ANALYZE
EXPLAIN ANALYZE SELECT * FROM products WHERE name LIKE 'Sam%';
-- Shows actual rows scanned vs. returned

Solutions for Suffix/Contains Searches:

like_optimization.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
-- SOLUTION 1: Full-Text Search (for contains)
-- PostgreSQL
CREATE INDEX idx_products_fts ON products USING gin(to_tsvector('english', name));
SELECT * FROM products WHERE to_tsvector('english', name) @@ to_tsquery('wireless');
 
-- MySQL
ALTER TABLE products ADD FULLTEXT INDEX ft_name (name);
SELECT * FROM products WHERE MATCH(name) AGAINST('wireless');
 
-- SOLUTION 2: Trigram indexes (PostgreSQL)
CREATE EXTENSION IF NOT EXISTS pg_trgm;
CREATE INDEX idx_products_name_trgm ON products USING gin(name gin_trgm_ops);
SELECT * FROM products WHERE name LIKE '%wireless%';
-- Now uses the trigram index!
 
-- SOLUTION 3: Reverse index for suffix searches
-- Store both name and REVERSE(name)
ALTER TABLE products ADD COLUMN name_reversed VARCHAR(255);
UPDATE products SET name_reversed = REVERSE(name);
CREATE INDEX idx_name_reversed ON products(name_reversed);
 
-- Suffix search becomes prefix search on reversed:
SELECT * FROM products WHERE name_reversed LIKE REVERSE('%Pro');
-- Equivalent to: WHERE name_reversed LIKE 'orP%'
 
-- SOLUTION 4: Computed/generated column with normalized data
ALTER TABLE products ADD COLUMN name_lower VARCHAR(255) 
  GENERATED ALWAYS AS (LOWER(name)) STORED;
CREATE INDEX idx_name_lower ON products(name_lower);

Full-Text Search for Contains

For searching within text content (blog posts, descriptions, documents), full-text search is vastly superior to LIKE '%term%'. It's faster, supports stemming (matching 'running' when searching 'run'), and provides relevance ranking.

Advanced Pattern Techniques

While LIKE is limited to % and _ wildcards, creative usage can solve surprisingly complex matching needs.

Multiple LIKE Conditions:

multiple_like.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
-- Multiple patterns with OR
SELECT * FROM products WHERE 
    name LIKE 'iPhone%' 
    OR name LIKE 'iPad%' 
    OR name LIKE 'Mac%';
-- Apple products starting with known prefixes
 
-- All conditions with AND
SELECT * FROM customers WHERE 
    name LIKE 'John%'
    AND email LIKE '%@gmail.com';
-- Johns with Gmail addresses
 
-- Exclusion patterns
SELECT * FROM products WHERE 
    name LIKE '%Pro%'
    AND name NOT LIKE '%Prototype%'
    AND name NOT LIKE '%Process%';
-- Products with 'Pro' but not those compound words
 
-- Complex multi-pattern filtering
SELECT * FROM error_logs WHERE 
    (message LIKE '%ERROR%' OR message LIKE '%FATAL%')
    AND message NOT LIKE '%DEBUG%'
    AND message NOT LIKE '%TEST%';

Format Validation with Underscore:

format_validation.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
-- Fixed-length format validation
SELECT * FROM products WHERE sku LIKE '___-___-____';
-- Matches: ABC-123-4567 (3 chars, dash, 3 chars, dash, 4 chars)
 
-- Credit card format (obfuscated example)
SELECT * FROM payments WHERE card_display LIKE '****-****-****-____';
-- Matches: ****-****-****-1234
 
-- License plate patterns
SELECT * FROM vehicles WHERE plate LIKE '___-____';
-- Matches: ABC-1234
 
-- Date-like format (not recommended for actual dates)
SELECT * FROM legacy_data WHERE date_string LIKE '____-__-__';
-- Matches: 2024-01-15 format
 
-- Combining fixed and variable parts
SELECT * FROM serial_numbers WHERE serial LIKE 'SN-____-%';
-- Matches: SN-1234-anything, SN-5678-more

Character Class Simulation (SQL Server):

SQL Server's LIKE supports a limited form of character classes using square brackets:

sql_server_brackets.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
-- SQL Server: Character ranges with []
SELECT * FROM products WHERE code LIKE '[A-Z][0-9][0-9][0-9]';
-- Matches: A123, B456, Z999 (letter followed by 3 digits)
 
SELECT * FROM users WHERE username LIKE '[a-zA-Z]%';
-- Usernames starting with a letter
 
SELECT * FROM codes WHERE code LIKE '[0-9][0-9][0-9]-[A-Z][A-Z]';
-- Matches: 123-AB, 999-ZZ
 
-- Negation with ^
SELECT * FROM data WHERE value LIKE '%[^0-9]%';
-- Values containing at least one non-digit
 
SELECT * FROM codes WHERE code LIKE '[^X-Z]%';
-- Codes NOT starting with X, Y, or Z
 
-- Character set
SELECT * FROM products WHERE code LIKE '[ABC]%';
-- Starting with A, B, or C only

Beyond LIKE: SIMILAR TO and Regular Expressions

When LIKE's simple wildcards aren't enough, SQL offers more powerful pattern matching tools.

SIMILAR TO (SQL Standard):

SIMILAR TO is a hybrid of LIKE and regular expressions, supported by PostgreSQL and some other databases:

similar_to.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
-- SIMILAR TO syntax (PostgreSQL)
-- Uses SQL-style regex: % and _ plus regex features
 
-- Alternation with |
SELECT * FROM products WHERE name SIMILAR TO '(iPhone|iPad|Mac)%';
-- Starting with iPhone, iPad, or Mac
 
-- Repetition with * + ?
SELECT * FROM codes WHERE code SIMILAR TO '[A-Z]+[0-9]*';
-- One or more letters, then zero or more digits
 
-- Character classes
SELECT * FROM data WHERE value SIMILAR TO '[0-9]{3}-[0-9]{4}';
-- Matches: 123-4567
 
-- Combining patterns
SELECT * FROM emails WHERE address SIMILAR TO '[a-z]+@[a-z]+\.(com|org|net)';
-- Simple email pattern
 
-- SIMILAR TO uses % for zero-or-more characters (like LIKE)
SELECT * FROM products WHERE name SIMILAR TO 'Pro%Book';
-- 'ProBook', 'ProductBook', 'ProMacBook'

Regular Expressions:

For full regex power, databases provide special operators:

regex_patterns.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
-- PostgreSQL: ~ operator (case-sensitive regex match)
SELECT * FROM emails WHERE address ~ '^[a-z]+@[a-z]+\.[a-z]{2,}$';
-- Full email validation pattern
 
SELECT * FROM emails WHERE address ~* 'gmail\.com$';
-- ~* is case-insensitive
 
-- Match any of several patterns
SELECT * FROM products WHERE name ~ '(wireless|bluetooth|wifi)';
-- Contains wireless, bluetooth, or wifi
 
-- PostgreSQL: !~ for NOT matching
SELECT * FROM logs WHERE message !~ '(DEBUG|TRACE)';
-- Exclude debug and trace messages
 
-- MySQL: REGEXP or RLIKE
SELECT * FROM products WHERE name REGEXP '^(iPhone|iPad)';
-- Starting with iPhone or iPad
 
SELECT * FROM emails WHERE address REGEXP '[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Z]{2,}';
-- Email pattern
 
-- Oracle: REGEXP_LIKE function
SELECT * FROM products WHERE REGEXP_LIKE(name, '^(iPhone|iPad)', 'i');
-- 'i' flag for case-insensitive
 
-- SQL Server: PATINDEX or CLR functions
-- Native regex not available; use PATINDEX for limited patterns
SELECT * FROM products WHERE PATINDEX('%[0-9]%', name) > 0;
-- Names containing at least one digit

Regex Performance

Regular expression matching is generally slower than LIKE and rarely uses indexes (except with special index types like trigram in PostgreSQL). For high-volume queries, consider pre-computing matches, using full-text search, or filtering with indexed LIKE first, then applying regex.

LIKE Best Practices

Following established best practices ensures your LIKE queries are correct, performant, and maintainable.

Performance Best Practices

•Prefer prefix patterns — 'prefix%' can use indexes; '%suffix' cannot
•Avoid leading wildcards on large tables — Consider full-text search or trigram indexes instead
•Create computed columns for transformed searches — Rather than LOWER(name) LIKE 'search%', add a name_lower column with an index
•Use EXPLAIN to verify — Check that your patterns actually use expected indexes
•Consider alternative data structures — Trie structures, Bloom filters for membership tests

Correctness Best Practices

•Always escape user input — Prevent unintended wildcard matching from user-provided search terms
•Know your collation — Verify case and accent sensitivity behavior
•Handle NULLs explicitly — Remember that NULL LIKE '%anything%' is UNKNOWN
•Test edge cases — Empty strings, very long strings, special characters
•Use ILIKE for intentional case-insensitivity — More explicit than LOWER()

like_best_practices.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
-- GOOD: Indexed prefix search
SELECT * FROM customers WHERE name LIKE 'Smith%';
 
-- BAD: Unindexed contains search on large table
SELECT * FROM documents WHERE content LIKE '%important%';
-- BETTER: Use full-text search
SELECT * FROM documents WHERE to_tsvector(content) @@ to_tsquery('important');
 
-- GOOD: Escaped user input
-- Application builds: userInput = "50%"
-- escapedInput = "50\%"
SELECT * FROM products WHERE name LIKE '%' || :escapedInput || '%' ESCAPE '\';
 
-- GOOD: Explicit NULL handling
SELECT * FROM products 
WHERE (description LIKE '%keyword%' OR description IS NULL);
 
-- GOOD: Case-insensitive with ILIKE (PostgreSQL)
SELECT * FROM products WHERE name ILIKE '%wireless%';
 
-- ALTERNATIVE: Pre-normalized column
SELECT * FROM products WHERE name_searchable LIKE '%wireless%';
-- Where name_searchable = LOWER(UNACCENT(name))

Summary and Module Conclusion

The LIKE operator provides essential pattern matching capabilities, and understanding its nuances makes the difference between effective searches and performance disasters.

LIKE Key Takeaways

•% matches zero or more characters; _ matches exactly one — These are the only LIKE wildcards
•Prefix patterns are indexable; leading wildcards are not — This drives most pattern design decisions
•Case sensitivity depends on collation — PostgreSQL uses ILIKE; MySQL defaults to case-insensitive
•Escape wildcards with ESCAPE clause — Essential for searching literal % or _ characters
•Full-text search handles contains patterns better — LIKE '%term%' scans entire tables
•Regular expressions extend LIKE's power — Use ~ (PostgreSQL) or REGEXP (MySQL) for complex patterns

Module 2 Complete: The WHERE Clause Mastered

You have now completed a comprehensive exploration of the WHERE clause—SQL's fundamental mechanism for filtering data. Let's consolidate the module's key concepts:

Module Summary: WHERE Clause Mastery

•Filtering Rows — WHERE operates on individual rows before grouping, using three-valued logic where NULL comparisons yield UNKNOWN
•Comparison Operators — Equality, inequality, and relational operators form the atoms of predicates, with NULL-specific operators (IS NULL, IS DISTINCT FROM) handling missing data
•BETWEEN — Inclusive range queries with critical timestamp boundary semantics; prefer half-open intervals for TIMESTAMP columns
•IN — Set membership testing with subquery support; the NOT IN NULL trap is one of SQL's most dangerous pitfalls
•LIKE Patterns — Flexible string matching with % and _ wildcards; index usage depends entirely on pattern structure

Module 2 Complete!

You now possess deep understanding of the WHERE clause—the foundational tool for data filtering in SQL. These skills form the backbone of effective query writing, enabling you to retrieve precisely the data you need while maintaining performance at scale.

5 / 5

Loading learning content...

Database Management SystemsWHERE Clause

The WHERE Clause: Filtering Data with Precision

LevelBeginner

Duration55 mins

TopicWHERE Clause

5 / 5

LIKE Patterns: Flexible String Matching

Beyond Exact Matches

What You Will Learn

LIKE Syntax and Wildcards

LIKE compares a string value against a pattern containing special wildcard characters. The basic syntax is:

column LIKE 'pattern'

Two Wildcard Characters:

LIKE Wildcard Characters
Wildcard	Meaning	Matches Examples
`%` (percent)	Zero or more characters	`'%test%'` matches 'test', 'testing', 'a test case', 'atestb'
`_` (underscore)	Exactly one character	`'_est'` matches 'test', 'best', 'rest'; NOT 'est' or 'atest'

like_basics.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
-- Prefix matching: Starts with
SELECT * FROM products WHERE name LIKE 'iPhone%';
-- Matches: iPhone, iPhone 15, iPhone 15 Pro Max
 
-- Suffix matching: Ends with
SELECT * FROM users WHERE email LIKE '%@gmail.com';
-- Matches: john@gmail.com, test.user@gmail.com
 
-- Contains: Anywhere in string
SELECT * FROM articles WHERE title LIKE '%database%';
-- Matches: Database Design, The database layer, building databases
 
-- Single character wildcard
SELECT * FROM products WHERE sku LIKE 'A_C';
-- Matches: ABC, A1C, A-C
-- Does NOT match: AC, ABBC, A12C
 
-- Combining wildcards
SELECT * FROM users WHERE phone LIKE '+1-___-___-____';
-- Matches: +1-555-123-4567 format
 
SELECT * FROM products WHERE code LIKE 'PRD_____';
-- Products with code starting 'PRD' followed by exactly 5 characters

Pattern Placement:

Common LIKE Patterns
Pattern	Description	Use Case
`'prefix%'`	Starts with 'prefix'	Autocomplete, prefix search
`'%suffix'`	Ends with 'suffix'	File extensions, domain matching
`'%contains%'`	Contains 'contains' anywhere	Keyword search, filtering
`'exact'`	Exact match (no wildcards)	Equivalent to = but slower
`'a_b'`	Three chars: a, any, b	Format validation
`'%\_test%'`	Contains literal underscore	Escape sequence usage

NOT LIKE: Pattern Exclusion

NOT LIKE finds strings that do NOT match a pattern. It's the logical negation of LIKE.

Basic Usage:

not_like.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
-- Exclude specific patterns
SELECT * FROM users WHERE email NOT LIKE '%@spam.com';
-- Users not from spam.com
 
SELECT * FROM products WHERE name NOT LIKE 'OBSOLETE%';
-- Products not marked obsolete
 
SELECT * FROM logs WHERE message NOT LIKE '%DEBUG%';
-- Non-debug log entries
 
-- Combine with other conditions
SELECT * FROM customers 
WHERE name NOT LIKE 'Test%' 
  AND name NOT LIKE '%DELETE%'
  AND email NOT LIKE '%@example.com';
-- Exclude test data patterns

NOT LIKE and NULL

not_like_null.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
-- NOT LIKE excludes NULLs silently
SELECT * FROM products WHERE description NOT LIKE '%special%';
-- Excludes: products containing 'special'
-- Also excludes: products where description IS NULL (often unintended)
 
-- Include NULLs when they should pass
SELECT * FROM products 
WHERE description NOT LIKE '%discontinued%' OR description IS NULL;
-- Products not marked discontinued, including those with no description
 
-- Alternative with COALESCE
SELECT * FROM products 
WHERE COALESCE(description, '') NOT LIKE '%discontinued%';
-- Treats NULL as empty string

Case Sensitivity in Pattern Matching

LIKE's case sensitivity depends on the column's collation—the rules that determine how strings are compared. This varies by database and configuration.

Database-Specific Behavior:

Default LIKE Case Sensitivity by Database
Database	Default Behavior	Case-Insensitive Option
PostgreSQL	Case-Sensitive	Use `ILIKE` operator
MySQL (default)	Case-Insensitive	Use `BINARY` keyword or binary collation
SQL Server	Depends on collation	Case-insensitive collations are common
Oracle	Case-Sensitive	Use `UPPER()` or `LOWER()`
SQLite	Case-Insensitive for ASCII	Only for ASCII letters by default

case_sensitivity.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
-- PostgreSQL: LIKE is case-sensitive
SELECT * FROM products WHERE name LIKE '%iphone%';
-- Matches: 'iphone', 'My iphone'
-- Does NOT match: 'iPhone', 'IPHONE'
 
-- PostgreSQL: ILIKE for case-insensitive
SELECT * FROM products WHERE name ILIKE '%iphone%';
-- Matches: 'iPhone', 'iphone', 'IPHONE', 'IpHoNe'
 
-- MySQL: LIKE is case-insensitive by default
SELECT * FROM products WHERE name LIKE '%iphone%';
-- Matches: 'iPhone', 'iphone', 'IPHONE' (all)
 
-- MySQL: Force case-sensitive with BINARY
SELECT * FROM products WHERE name LIKE BINARY '%iPhone%';
-- Only matches exact case
 
-- Portable case-insensitive pattern
SELECT * FROM products WHERE LOWER(name) LIKE LOWER('%iPhone%');
-- Works everywhere but may not use indexes
 
-- Better performance: Normalize data on insert
-- Store name_lower as a computed/generated column
-- CREATE INDEX idx_name_lower ON products(name_lower);
SELECT * FROM products WHERE name_lower LIKE 'iphone%';

Collation Affects Wildcards Too

With accent-insensitive collations, 'café' might match 'cafe'. With case-insensitive collations, 'ABC' matches 'abc'. Know your collation settings to predict LIKE behavior accurately.

Escape Sequences for Literal Wildcards

What if you need to search for a literal % or _ character? Since these are wildcards, they need to be escaped when you want to match them literally.

The ESCAPE Clause:

escape_basic.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
-- Problem: Find codes containing underscore
SELECT * FROM products WHERE code LIKE '%_%';
-- This matches EVERYTHING (underscore matches any single character)
 
-- Solution: Use ESCAPE clause
SELECT * FROM products WHERE code LIKE '%\_%' ESCAPE '\';
-- The backslash escapes the underscore
-- Matches: 'A_B', 'CODE_123', 'TEST_ITEM'
 
-- Escape the percent sign
SELECT * FROM discounts WHERE description LIKE '%50\%%' ESCAPE '\';
-- Matches: '50% off', 'Save 50%!'
 
-- Choose any escape character
SELECT * FROM codes WHERE code LIKE '%|_%' ESCAPE '|';
-- Using | as escape character
 
SELECT * FROM patterns WHERE pattern LIKE '%!%%' ESCAPE '!';
-- Using ! as escape character

Escaping the Escape Character:

If you need to match the escape character itself, double it:

escape_advanced.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
-- Match literal backslash
SELECT * FROM paths WHERE path LIKE '%\\%' ESCAPE '\';
-- Matches paths containing backslash
-- Note: Some databases may require additional escaping for string literals
 
-- Match backslash followed by underscore
SELECT * FROM data WHERE text LIKE '%\\_char%' ESCAPE '\';
-- Matches: 'has\_char_here'
 
-- Database-specific escape handling
-- PostgreSQL: Standard ESCAPE clause works
SELECT * FROM products WHERE name LIKE '%_%' ESCAPE '';
 
-- SQL Server: Square brackets can escape
SELECT * FROM products WHERE name LIKE '%[_]%';
-- [_] matches literal underscore
SELECT * FROM products WHERE name LIKE '%[%]%';
-- [%] matches literal percent
 
-- MySQL: Default escape is backslash
SELECT * FROM products WHERE name LIKE '%\_%';
-- Backslash escape is default, ESCAPE clause optional

User Input and LIKE Injection

user_input_escape.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
-- Building safe LIKE patterns from user input
-- Pseudocode for application layer:
 
-- user_input = "50% off"
-- escaped_input = user_input.replace('%', '\%').replace('_', '\_')
-- query = "SELECT * FROM products WHERE description LIKE '%' + escaped_input + '%' ESCAPE '\'"
 
-- Example in PostgreSQL with parameter
PREPARE find_text(text) AS
  SELECT * FROM products 
  WHERE description LIKE '%' || replace(replace($1, '%', '\%'), '_', '\_') || '%' ESCAPE '\';
 
EXECUTE find_text('50% off');
-- Correctly searches for literal "50% off"

LIKE Performance and Index Usage

LIKE's performance depends critically on where the wildcard appears. This determines whether indexes can be used.

The Prefix Rule:

A B-tree index can only be used when the pattern has a fixed prefix (no leading wildcard).

LIKE Pattern Indexability
Pattern	Index Usable?	Reason
`'prefix%'`	✅ Yes	Fixed prefix enables index range scan
`'%suffix'`	❌ No	Leading wildcard requires full scan
`'%contains%'`	❌ No	Leading wildcard requires full scan
`'pre%fix'`	Partial	Uses index for 'pre', filters rest
`'exact'`	✅ Yes	Equivalent to = (equality)
`'_BC%'`	❌ No	Leading _ is still a leading wildcard

like_index_usage.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
-- Create index for these examples
CREATE INDEX idx_products_name ON products(name);
 
-- INDEXABLE: Prefix pattern
EXPLAIN SELECT * FROM products WHERE name LIKE 'iPhone%';
-- Uses: Index range scan on idx_products_name
-- Execution: Seek to 'iPhone', scan while name < 'iPhonf'
 
-- NOT INDEXABLE: Suffix pattern
EXPLAIN SELECT * FROM products WHERE name LIKE '%Pro';
-- Uses: Sequential scan (table scan)
-- Must examine every row
 
-- NOT INDEXABLE: Contains pattern
EXPLAIN SELECT * FROM products WHERE name LIKE '%wireless%';
-- Uses: Sequential scan
-- No way to use index for arbitrary substring
 
-- PARTIAL USE: Pattern with fixed prefix
EXPLAIN SELECT * FROM products WHERE name LIKE 'Apple%Watch%';
-- Uses: Index range scan for 'Apple%'
-- Then filters: remaining rows for '%Watch%'
 
-- Verify with EXPLAIN ANALYZE
EXPLAIN ANALYZE SELECT * FROM products WHERE name LIKE 'Sam%';
-- Shows actual rows scanned vs. returned

Solutions for Suffix/Contains Searches:

like_optimization.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
-- SOLUTION 1: Full-Text Search (for contains)
-- PostgreSQL
CREATE INDEX idx_products_fts ON products USING gin(to_tsvector('english', name));
SELECT * FROM products WHERE to_tsvector('english', name) @@ to_tsquery('wireless');
 
-- MySQL
ALTER TABLE products ADD FULLTEXT INDEX ft_name (name);
SELECT * FROM products WHERE MATCH(name) AGAINST('wireless');
 
-- SOLUTION 2: Trigram indexes (PostgreSQL)
CREATE EXTENSION IF NOT EXISTS pg_trgm;
CREATE INDEX idx_products_name_trgm ON products USING gin(name gin_trgm_ops);
SELECT * FROM products WHERE name LIKE '%wireless%';
-- Now uses the trigram index!
 
-- SOLUTION 3: Reverse index for suffix searches
-- Store both name and REVERSE(name)
ALTER TABLE products ADD COLUMN name_reversed VARCHAR(255);
UPDATE products SET name_reversed = REVERSE(name);
CREATE INDEX idx_name_reversed ON products(name_reversed);
 
-- Suffix search becomes prefix search on reversed:
SELECT * FROM products WHERE name_reversed LIKE REVERSE('%Pro');
-- Equivalent to: WHERE name_reversed LIKE 'orP%'
 
-- SOLUTION 4: Computed/generated column with normalized data
ALTER TABLE products ADD COLUMN name_lower VARCHAR(255) 
  GENERATED ALWAYS AS (LOWER(name)) STORED;
CREATE INDEX idx_name_lower ON products(name_lower);

Full-Text Search for Contains

Advanced Pattern Techniques

While LIKE is limited to % and _ wildcards, creative usage can solve surprisingly complex matching needs.

Multiple LIKE Conditions:

multiple_like.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
-- Multiple patterns with OR
SELECT * FROM products WHERE 
    name LIKE 'iPhone%' 
    OR name LIKE 'iPad%' 
    OR name LIKE 'Mac%';
-- Apple products starting with known prefixes
 
-- All conditions with AND
SELECT * FROM customers WHERE 
    name LIKE 'John%'
    AND email LIKE '%@gmail.com';
-- Johns with Gmail addresses
 
-- Exclusion patterns
SELECT * FROM products WHERE 
    name LIKE '%Pro%'
    AND name NOT LIKE '%Prototype%'
    AND name NOT LIKE '%Process%';
-- Products with 'Pro' but not those compound words
 
-- Complex multi-pattern filtering
SELECT * FROM error_logs WHERE 
    (message LIKE '%ERROR%' OR message LIKE '%FATAL%')
    AND message NOT LIKE '%DEBUG%'
    AND message NOT LIKE '%TEST%';

Format Validation with Underscore:

format_validation.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
-- Fixed-length format validation
SELECT * FROM products WHERE sku LIKE '___-___-____';
-- Matches: ABC-123-4567 (3 chars, dash, 3 chars, dash, 4 chars)
 
-- Credit card format (obfuscated example)
SELECT * FROM payments WHERE card_display LIKE '****-****-****-____';
-- Matches: ****-****-****-1234
 
-- License plate patterns
SELECT * FROM vehicles WHERE plate LIKE '___-____';
-- Matches: ABC-1234
 
-- Date-like format (not recommended for actual dates)
SELECT * FROM legacy_data WHERE date_string LIKE '____-__-__';
-- Matches: 2024-01-15 format
 
-- Combining fixed and variable parts
SELECT * FROM serial_numbers WHERE serial LIKE 'SN-____-%';
-- Matches: SN-1234-anything, SN-5678-more

Character Class Simulation (SQL Server):

SQL Server's LIKE supports a limited form of character classes using square brackets:

sql_server_brackets.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
-- SQL Server: Character ranges with []
SELECT * FROM products WHERE code LIKE '[A-Z][0-9][0-9][0-9]';
-- Matches: A123, B456, Z999 (letter followed by 3 digits)
 
SELECT * FROM users WHERE username LIKE '[a-zA-Z]%';
-- Usernames starting with a letter
 
SELECT * FROM codes WHERE code LIKE '[0-9][0-9][0-9]-[A-Z][A-Z]';
-- Matches: 123-AB, 999-ZZ
 
-- Negation with ^
SELECT * FROM data WHERE value LIKE '%[^0-9]%';
-- Values containing at least one non-digit
 
SELECT * FROM codes WHERE code LIKE '[^X-Z]%';
-- Codes NOT starting with X, Y, or Z
 
-- Character set
SELECT * FROM products WHERE code LIKE '[ABC]%';
-- Starting with A, B, or C only

Beyond LIKE: SIMILAR TO and Regular Expressions

When LIKE's simple wildcards aren't enough, SQL offers more powerful pattern matching tools.

SIMILAR TO (SQL Standard):

SIMILAR TO is a hybrid of LIKE and regular expressions, supported by PostgreSQL and some other databases:

similar_to.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
-- SIMILAR TO syntax (PostgreSQL)
-- Uses SQL-style regex: % and _ plus regex features
 
-- Alternation with |
SELECT * FROM products WHERE name SIMILAR TO '(iPhone|iPad|Mac)%';
-- Starting with iPhone, iPad, or Mac
 
-- Repetition with * + ?
SELECT * FROM codes WHERE code SIMILAR TO '[A-Z]+[0-9]*';
-- One or more letters, then zero or more digits
 
-- Character classes
SELECT * FROM data WHERE value SIMILAR TO '[0-9]{3}-[0-9]{4}';
-- Matches: 123-4567
 
-- Combining patterns
SELECT * FROM emails WHERE address SIMILAR TO '[a-z]+@[a-z]+\.(com|org|net)';
-- Simple email pattern
 
-- SIMILAR TO uses % for zero-or-more characters (like LIKE)
SELECT * FROM products WHERE name SIMILAR TO 'Pro%Book';
-- 'ProBook', 'ProductBook', 'ProMacBook'

Regular Expressions:

For full regex power, databases provide special operators:

regex_patterns.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
-- PostgreSQL: ~ operator (case-sensitive regex match)
SELECT * FROM emails WHERE address ~ '^[a-z]+@[a-z]+\.[a-z]{2,}$';
-- Full email validation pattern
 
SELECT * FROM emails WHERE address ~* 'gmail\.com$';
-- ~* is case-insensitive
 
-- Match any of several patterns
SELECT * FROM products WHERE name ~ '(wireless|bluetooth|wifi)';
-- Contains wireless, bluetooth, or wifi
 
-- PostgreSQL: !~ for NOT matching
SELECT * FROM logs WHERE message !~ '(DEBUG|TRACE)';
-- Exclude debug and trace messages
 
-- MySQL: REGEXP or RLIKE
SELECT * FROM products WHERE name REGEXP '^(iPhone|iPad)';
-- Starting with iPhone or iPad
 
SELECT * FROM emails WHERE address REGEXP '[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Z]{2,}';
-- Email pattern
 
-- Oracle: REGEXP_LIKE function
SELECT * FROM products WHERE REGEXP_LIKE(name, '^(iPhone|iPad)', 'i');
-- 'i' flag for case-insensitive
 
-- SQL Server: PATINDEX or CLR functions
-- Native regex not available; use PATINDEX for limited patterns
SELECT * FROM products WHERE PATINDEX('%[0-9]%', name) > 0;
-- Names containing at least one digit

Regex Performance

LIKE Best Practices

Following established best practices ensures your LIKE queries are correct, performant, and maintainable.

Performance Best Practices

•Prefer prefix patterns — 'prefix%' can use indexes; '%suffix' cannot
•Avoid leading wildcards on large tables — Consider full-text search or trigram indexes instead
•Create computed columns for transformed searches — Rather than LOWER(name) LIKE 'search%', add a name_lower column with an index
•Use EXPLAIN to verify — Check that your patterns actually use expected indexes
•Consider alternative data structures — Trie structures, Bloom filters for membership tests

Correctness Best Practices

•Always escape user input — Prevent unintended wildcard matching from user-provided search terms
•Know your collation — Verify case and accent sensitivity behavior
•Handle NULLs explicitly — Remember that NULL LIKE '%anything%' is UNKNOWN
•Test edge cases — Empty strings, very long strings, special characters
•Use ILIKE for intentional case-insensitivity — More explicit than LOWER()

like_best_practices.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
-- GOOD: Indexed prefix search
SELECT * FROM customers WHERE name LIKE 'Smith%';
 
-- BAD: Unindexed contains search on large table
SELECT * FROM documents WHERE content LIKE '%important%';
-- BETTER: Use full-text search
SELECT * FROM documents WHERE to_tsvector(content) @@ to_tsquery('important');
 
-- GOOD: Escaped user input
-- Application builds: userInput = "50%"
-- escapedInput = "50\%"
SELECT * FROM products WHERE name LIKE '%' || :escapedInput || '%' ESCAPE '\';
 
-- GOOD: Explicit NULL handling
SELECT * FROM products 
WHERE (description LIKE '%keyword%' OR description IS NULL);
 
-- GOOD: Case-insensitive with ILIKE (PostgreSQL)
SELECT * FROM products WHERE name ILIKE '%wireless%';
 
-- ALTERNATIVE: Pre-normalized column
SELECT * FROM products WHERE name_searchable LIKE '%wireless%';
-- Where name_searchable = LOWER(UNACCENT(name))

Summary and Module Conclusion

The LIKE operator provides essential pattern matching capabilities, and understanding its nuances makes the difference between effective searches and performance disasters.

LIKE Key Takeaways

•% matches zero or more characters; _ matches exactly one — These are the only LIKE wildcards
•Prefix patterns are indexable; leading wildcards are not — This drives most pattern design decisions
•Case sensitivity depends on collation — PostgreSQL uses ILIKE; MySQL defaults to case-insensitive
•Escape wildcards with ESCAPE clause — Essential for searching literal % or _ characters
•Full-text search handles contains patterns better — LIKE '%term%' scans entire tables
•Regular expressions extend LIKE's power — Use ~ (PostgreSQL) or REGEXP (MySQL) for complex patterns

Module 2 Complete: The WHERE Clause Mastered

You have now completed a comprehensive exploration of the WHERE clause—SQL's fundamental mechanism for filtering data. Let's consolidate the module's key concepts:

Module Summary: WHERE Clause Mastery

•Filtering Rows — WHERE operates on individual rows before grouping, using three-valued logic where NULL comparisons yield UNKNOWN
•Comparison Operators — Equality, inequality, and relational operators form the atoms of predicates, with NULL-specific operators (IS NULL, IS DISTINCT FROM) handling missing data
•BETWEEN — Inclusive range queries with critical timestamp boundary semantics; prefer half-open intervals for TIMESTAMP columns
•IN — Set membership testing with subquery support; the NOT IN NULL trap is one of SQL's most dangerous pitfalls
•LIKE Patterns — Flexible string matching with % and _ wildcards; index usage depends entirely on pattern structure

Module 2 Complete!

5 / 5