Operators and Pattern Matching

The Limits of Equality

The = operator performs exact matching. For analytical questions about text, exact matching is rarely enough. Consider these questions:

• "Find all customers whose email is from a specific domain."
• "Find all products whose name contains 'pro'."
• "Find all orders where the reference number starts with 'WEB'."

These are pattern questions, not equality questions. SQL answers them with LIKE.

LIKE Syntax and Wildcards

LIKE compares a string against a pattern. The pattern can contain two wildcards:

• % matches any sequence of zero or more characters.
• _ matches exactly one character.

-- Emails ending with '@company.com'
SELECT customer_id, email
FROM customers
WHERE email LIKE '%@company.com';

-- Product names starting with 'Pro'
SELECT product_id, product_name
FROM products
WHERE product_name LIKE 'Pro%';

-- Reference numbers like 'WEB-1234' (WEB-, then exactly 4 characters)
SELECT order_id, reference
FROM orders
WHERE reference LIKE 'WEB-____';

The % wildcard is the workhorse. The _ wildcard is useful when you know the exact length of a segment.

LIKE Is Case-Sensitive

In PostgreSQL and SQLite, LIKE is case-sensitive. 'Pro%' matches 'Professional' but not 'professional'. In MySQL, LIKE is case-insensitive by default (depending on the collation). This inconsistency means you cannot rely on LIKE for case-insensitive matching in portable code.

Negating Patterns

NOT LIKE excludes rows matching the pattern:

-- All emails NOT from company.com
SELECT customer_id, email
FROM customers
WHERE email NOT LIKE '%@company.com';

NOT LIKE has the same NULL trap as NOT IN. If email is NULL, email NOT LIKE '%@company.com' evaluates to UNKNOWN, and the row is excluded. Add OR email IS NULL if you want to keep those rows.

Performance: Leading Wildcards Kill Indexes

WHERE email LIKE '%@company.com' cannot use a standard B-tree index on email. The database must scan every row because the pattern does not have a fixed prefix. This is called a leading wildcard, and it forces a full table scan.

WHERE email LIKE 'john.%@company.com' can use an index because the pattern starts with a fixed prefix. The database navigates the index to the 'john.' prefix and scans only that range.

The rule: avoid leading wildcards on large tables. If you need suffix matching, consider storing a reversed copy of the column and indexing it, or use a full-text search index.

The Case Problem

Real-world text data is inconsistently cased. 'John Smith', 'john smith', and 'JOHN SMITH' are the same person but three different strings. A pattern match that is case-sensitive will miss two of them.

ILIKE in PostgreSQL

PostgreSQL provides ILIKE, which is LIKE with case-insensitive matching:

-- Case-insensitive match on product name
SELECT product_id, product_name
FROM products
WHERE product_name ILIKE '%pro%';

This matches 'Pro', 'PRO', 'pro', 'Professional', 'PRODUCT', and any other casing variant. It is the simplest solution when the database supports it.

Case-Insensitive Matching Without ILIKE

MySQL's LIKE is already case-insensitive (with default collations). SQL Server and Oracle do not have ILIKE. The portable solution is to normalize both sides with LOWER or UPPER:

-- Portable case-insensitive matching
SELECT product_id, product_name
FROM products
WHERE LOWER(product_name) LIKE LOWER('%Pro%');

This works on every database. The cost is that LOWER(product_name) cannot use a standard index on product_name. For large tables, create a functional index on LOWER(product_name):

CREATE INDEX idx_product_name_lower ON products (LOWER(product_name));

The index stores the lowercased values, so the query can use it.

Collation vs Function

Some databases support case-insensitive collations. In MySQL, a column with utf8mb4_general_ci collation (the _ci suffix means case-insensitive) makes LIKE case-insensitive without any function call. In SQL Server, you can specify COLLATE Latin1_General_CI_AS in the query.

Collation-based matching is faster than function-based matching because it can use the existing index. But it is database-specific and less portable. Choose based on your deployment constraints.

When to Use ILIKE vs LOWER

• Use ILIKE when you are on PostgreSQL and want readable code.
• Use LOWER(col) LIKE LOWER(pattern) when you need portability.
• Use a case-insensitive collation when you control the schema and want performance.
• Use a functional index on LOWER(col) when you need case-insensitive matching on a large table and cannot change the collation.

Common Case-Insensitive Patterns

-- Find all variations of 'admin' in usernames
WHERE LOWER(username) LIKE '%admin%'

-- Find emails from a domain, any casing
WHERE LOWER(email) LIKE '%@COMPANY.COM'

-- Find names starting with 'mc' or 'mac' (Scottish surnames)
WHERE LOWER(last_name) LIKE 'mc%' OR LOWER(last_name) LIKE 'mac%'

The last example shows a limitation: LIKE cannot express alternation within a single pattern. For complex alternation, use regular expressions (covered below).

Slicing Numbers into Segments

Analytical reports often segment numeric data into bands: age groups, price tiers, revenue brackets. Range predicates are the tool for this.

Comparison Operators for Ranges

The basic building blocks are the comparison operators combined with AND:

-- Orders between $100 and $500
SELECT order_id, total_amount
FROM orders
WHERE total_amount >= 100 AND total_amount <= 500;

This is a closed range — both endpoints are included. For continuous values, a half-open range is often more correct:

-- Orders from $100 up to (but not including) $500
WHERE total_amount >= 100 AND total_amount < 500;

The half-open pattern prevents a value exactly at $500 from appearing in two adjacent segments.

BETWEEN for Closed Ranges

BETWEEN is the shorthand for a closed range:

WHERE total_amount BETWEEN 100 AND 500;
-- Equivalent to: total_amount >= 100 AND total_amount <= 500

Use BETWEEN when the range is genuinely inclusive on both ends — for example, age groups (18-25, 26-35) or date ranges where the end date is the last day of a period.

Building Segments with CASE

For non-contiguous or overlapping segments, CASE is more flexible than range predicates:

SELECT
    order_id,
    total_amount,
    CASE
        WHEN total_amount < 100 THEN 'under_100'
        WHEN total_amount < 500 THEN '100_to_499'
        WHEN total_amount < 1000 THEN '500_to_999'
        ELSE '1000_plus'
    END AS price_segment
FROM orders;

The CASE expression evaluates conditions in order and returns the first match. This is the standard pattern for bucketing. The conditions must be mutually exclusive and ordered from most specific to least specific (or vice versa, but be consistent).

Date Range Segments

Date ranges are the most common segmentation in analytical reports:

-- Orders in Q1 2024
WHERE order_date >= '2024-01-01' AND order_date < '2024-04-01'

-- Orders in the last 30 days
WHERE order_date >= CURRENT_DATE - INTERVAL '30 days'

-- Orders in the current month
WHERE order_date >= DATE_TRUNC('month', CURRENT_DATE)
  AND order_date < DATE_TRUNC('month', CURRENT_DATE) + INTERVAL '1 month'

The half-open pattern (>= start AND < end) is the correct approach for all date ranges. It handles leap years, month boundaries, and time components without special cases.

Overlapping Ranges and Priority

When segments can overlap, the order of evaluation matters. CASE evaluates top to bottom. Range predicates with AND are simultaneous. Choose the structure that matches the business logic:

-- Priority: VIP customers get the 'vip' segment regardless of spend
CASE
    WHEN customer_tier = 'vip' THEN 'vip'
    WHEN total_amount >= 1000 THEN 'high_value'
    WHEN total_amount >= 100 THEN 'medium_value'
    ELSE 'low_value'
END

This query assigns 'vip' to all VIP customers, then segments the rest by spend. The order of conditions encodes the business priority.

The Injection-Adjacent Problem

When a pattern comes from user input — a search box, a filter parameter — the user may type wildcard characters. A search for '100%' should match the literal string '100%', not '100' followed by anything.

ESCAPE Clause

SQL provides the ESCAPE clause to define an escape character:

-- Find products with '100%' in the name (literal percent sign)
SELECT product_id, product_name
FROM products
WHERE product_name LIKE '%100\%%' ESCAPE '\';

The pattern %100\%% breaks down as:

• % — match any leading characters
• 100 — match '100' literally
• \% — match a literal '%' (the backslash escapes it)
• % — match any trailing characters

Without the ESCAPE clause, \% would be interpreted as '%' followed by '%', which is not what you want.

Choosing an Escape Character

The escape character must not appear in the pattern except as an escape. Backslash is the common choice, but in some contexts (like Java strings or JSON), backslash itself needs escaping. Alternatives include !, ~, and |:

WHERE product_name LIKE '%100!%%' ESCAPE '!'

Pick a character that does not appear in your data and is easy to type.

Escaping Underscore

The underscore is also a wildcard. If user input might contain underscores that should be matched literally:

-- Find files named 'report_2024' (literal underscore)
SELECT file_id, file_name
FROM files
WHERE file_name LIKE '%report\_2024%' ESCAPE '\';

Without escaping, report_2024 would also match 'reportX2024' because _ matches any single character.

Parameterized Queries and Patterns

When using parameterized queries (which you should always do for security), the pattern is built in application code:

# Python example
search_term = "100%"
pattern = f"%{search_term.replace('%', '\\%').replace('_', '\\_')}%"
cursor.execute(
    "SELECT * FROM products WHERE product_name LIKE ? ESCAPE '\\'",
    (pattern,)
)

This escapes wildcards in the user input before passing it as a parameter. The database receives a safe pattern. This is separate from SQL injection prevention — parameterization handles injection, and escaping handles wildcard semantics.

When Not to Escape

If the pattern is a fixed string in your code (not user input), you do not need to escape wildcards unless they appear literally in the pattern. Hardcoded patterns like '%@company.com' contain no wildcards that need escaping.

Decision Framework

SQL offers several pattern-matching tools. Choosing the right one depends on the question, the data size, and the database.

Regular Expressions

PostgreSQL and MySQL support regular expressions for complex patterns:

-- PostgreSQL: match emails with a valid format
WHERE email ~ '^[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Za-z]{2,}$'

-- MySQL: same pattern
WHERE email REGEXP '^[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Za-z]{2,}$'

Regular expressions are powerful but slow. They cannot use standard indexes. Use them for validation and complex matching, not for high-volume filtering.

Full-Text Search

For natural language queries — "find all documents mentioning 'database optimization'" — full-text search is the right tool. It handles stemming, ranking, and stop words:

-- PostgreSQL full-text search
SELECT title, content
FROM articles
WHERE to_tsvector('english', content) @@ to_tsquery('database & optimization');

Full-text search is a separate topic, but it is worth knowing that it exists. LIKE is not a search engine.

Practical Guidelines

1. Use = when you can. It is the fastest and most index-friendly.
2. Use LIKE 'prefix%' when you can. It uses indexes and answers most "starts with" questions.
3. Avoid LIKE '%...' on large tables. If you must, consider a full-text index or a reversed-column index.
4. Use ILIKE or LOWER for case-insensitive matching. Choose based on portability needs.
5. Escape wildcards in user input. Always. A user typing '%' should not get a full table scan.
6. Use CASE for segmentation. It is more flexible than range predicates for non-contiguous bands.
7. Use regular expressions sparingly. They are powerful but slow and hard to read.

What You Should Remember

Pattern matching is where analytical SQL meets real-world data messiness. The tools are simple — LIKE, ILIKE, range predicates, CASE — but the decisions around them (case sensitivity, wildcard escaping, index usage) are what separate a query that works from a query that works correctly and efficiently at scale.