Pattern Matching — LIKE & Wildcards

LIKE is a SQL comparison operator used in WHERE clauses to match string values against a pattern rather than an exact value. Instead of finding rows where a column equals a specific string, LIKE finds rows where the column matches a pattern that can include wildcard characters standing in for unknown characters.

LIKE has two wildcard characters. The percent sign % matches any sequence of zero or more characters — it is the most common wildcard and stands in for "anything could be here." LIKE 'Amul%' matches 'Amul Butter', 'Amul Milk', 'Amul Fresh Paneer', and any string starting with 'Amul'. LIKE '%gmail.com' matches any string ending with 'gmail.com'. LIKE '%Fresh%' matches any string containing 'Fresh' anywhere.

The underscore _ matches exactly one character — any character. LIKE 'ST00_' matches 'ST001' through 'ST009' but not 'ST010' (which has two digits after 'ST00'). LIKE '____' matches any string of exactly four characters. LIKE '_ata' matches 'Tata', 'Data', 'Bata' — any four-character string ending in 'ata'. The two wildcards can be combined: LIKE '_a%' matches any string where the second character is 'a', regardless of what comes before or after.

LIKE performs case-sensitive pattern matching in PostgreSQL, DuckDB, and SQLite — the pattern must match the case of the stored value exactly. LIKE 'amul%' does not match 'Amul Butter' in PostgreSQL because 'a' does not equal 'A'. LIKE 'Amul%' matches 'Amul Butter' correctly.

ILIKE is a PostgreSQL-specific operator that performs case-insensitive pattern matching. ILIKE 'amul%' matches 'Amul Butter', 'AMUL MILK', 'amul ghee', and any other variation of case. ILIKE 'AMUL%' also matches all the same values — the pattern and the data are both treated as if they were lowercase before comparison.

MySQL's LIKE is case-insensitive by default (controlled by the collation), so MySQL LIKE behaves like PostgreSQL ILIKE for standard ASCII characters. SQL Server's case sensitivity also depends on the database collation. For cross-database compatible case-insensitive pattern matching, use LOWER() on both the column and pattern: WHERE LOWER(column) LIKE LOWER('pattern%'). This works identically on all databases but prevents index usage on the column. The recommendation: use ILIKE in PostgreSQL for case-insensitive searches, and use LOWER() only when writing SQL that must run on multiple database systems.

LIKE '%pattern%' (with a leading %) is slow because the leading percent wildcard prevents the database from using a B-tree index on the column. A B-tree index organises values in sorted order by their prefix — it can efficiently find all values starting with 'Amul' because those values are contiguous in the index. But for '%Fresh%', the database does not know where in the sorted index the matching values are — they could be anywhere. The database must read every row, retrieve the full value, and check whether it contains 'Fresh'. This is a full table scan: O(n) where n is the number of rows. On a table with 10 million products, this takes seconds.

The production alternatives depend on the scale and the database. For moderate scale (up to a few million rows), PostgreSQL's built-in full-text search using tsvector columns and GIN indexes can make contains-searches fast by pre-computing searchable tokens from text columns. A GIN index on tsvector supports fast text search without reading every row. MySQL supports FULLTEXT indexes with similar capabilities.

For large scale — millions of products, user-facing search with sub-100ms latency requirements — dedicated search engines like Elasticsearch, OpenSearch, or Typesense are the production standard. These systems are built specifically for full-text and fuzzy matching and are used by Amazon, Sephora, Amazon India, and every major e-commerce platform for their product search. They run alongside the SQL database: SQL handles transactions and structured queries, the search engine handles text search. LIKE '%pattern%' is acceptable for small tables and ad-hoc queries, but should never be the foundation of a production search feature on large datasets.

Since % and _ are wildcard characters with special meaning in LIKE patterns, you must escape them when you want to match them as literal characters. SQL provides the ESCAPE clause for this purpose: LIKE 'pattern' ESCAPE 'escape_char'. The escape character is a single character you define — commonly backslash \ or exclamation mark !. Any wildcard that follows the escape character is treated as a literal character instead of a wildcard.

Example: to find product descriptions containing "100%", write WHERE description LIKE '%100\%%' ESCAPE '\'. The first % is a wildcard (match anything before "100"), the \% is a literal percent sign, and the final % is another wildcard (match anything after). Similarly, to find values containing a literal underscore: WHERE code LIKE '%\_order%' ESCAPE '\' — the \_ is a literal underscore.

In PostgreSQL, the backslash already has special meaning in string literals, so you may need to double it: LIKE '%100\\%%' ESCAPE '\\'. Using a non-standard escape character like ! avoids this: WHERE description LIKE '%100!%%' ESCAPE '!'. This is cleaner and avoids backslash confusion. In practice, the need to search for literal % or _ is rare in well-designed databases — values that users search for usually do not contain SQL wildcard characters. When they do appear (in financial data with percentage signs, for example), the ESCAPE clause is the correct solution rather than any workaround.

LIKE is the standard SQL pattern matching operator supported by all relational databases. It uses only two wildcards (% and _), making it simple and fast for straightforward patterns. LIKE with a leading literal (not a wildcard) can use B-tree indexes and is performant at scale. LIKE is the right choice for the vast majority of pattern matching needs.

SIMILAR TO is a PostgreSQL-only operator that extends LIKE with a limited regex-like syntax: | for alternation, [] for character classes, * for zero or more repetitions, + for one or more, ? for zero or one. SIMILAR TO '%@(gmail|yahoo)\.com' matches email addresses from either domain. SIMILAR TO is anchored — the pattern must match the entire string. It is more powerful than LIKE but less commonly used because ILIKE handles most needs, and full regular expressions are more expressive when SIMILAR TO is not enough. SIMILAR TO is always a full scan — it offers no index benefit.

Regular expressions (REGEXP in MySQL, ~ in PostgreSQL) are the most powerful text matching tool — they support the full regular expression syntax: lookaheads, backreferences, named groups, character classes, quantifiers. They handle complex validation patterns (PAN card format, SSN format, phone number patterns), multi-pattern alternation, and any matching logic LIKE cannot express. Regular expressions are always slow — full scans, no index benefit — and have complex, easy-to-get-wrong syntax. Use them when LIKE cannot solve the problem and the table is small enough that the scan cost is acceptable. For production search on large tables, none of the three are the right choice — use full-text search indexes or a dedicated search engine instead.

Cause: Case sensitivity mismatch. In PostgreSQL and DuckDB (this playground), LIKE is case-sensitive. The pattern 'amul%' does not match 'Amul Butter' because 'a' ≠ 'A'. The stored values use title case (first letter capitalised) but the pattern uses lowercase. No error is thrown — the query simply returns zero rows.

Fix: Use ILIKE instead of LIKE for case-insensitive matching in PostgreSQL and DuckDB: WHERE name ILIKE 'amul%'. For cross-database compatibility, use LOWER(): WHERE LOWER(name) LIKE 'amul%'. Always run SELECT DISTINCT column FROM table to see the actual stored values before writing a LIKE condition — this immediately reveals case and spacing issues that would cause LIKE to miss rows.

Cause: A leading percent wildcard prevents index usage. LIKE '%search term%' requires the database to scan every row and check the full value — a full table scan. On a table with millions of rows and no full-text index, this takes seconds to minutes. The EXPLAIN plan will show Seq Scan (sequential scan) with an estimated cost proportional to the table size.

Fix: Short-term: add a WHERE condition on an indexed column before the LIKE to reduce the scan size — WHERE category = 'Electronics' AND description LIKE '%waterproof%' limits the LIKE scan to only Electronics products. Long-term: add a PostgreSQL full-text search index (GIN on tsvector column) or switch to a search engine (Elasticsearch, Typesense) for any feature that users will search. For ends-with patterns specifically, consider storing a reversed copy of the column and using LIKE 'reversed_pattern%' on the reversed column — which can use an index.

Cause: NOT LIKE excludes NULL rows silently. If the column being matched can contain NULL, rows where the column IS NULL evaluate NOT LIKE to NULL (not TRUE), and are excluded from results by the WHERE clause. This is the same NULL propagation issue as NOT IN — any NULL in the comparison column causes the row to silently disappear.

Fix: Add OR column IS NULL to include null rows: WHERE name NOT LIKE 'Amul%' OR name IS NULL. To diagnose: run SELECT COUNT(*) FROM table WHERE column IS NULL — if this returns any rows, your NOT LIKE is excluding them. Decide whether NULL rows should be included in the NOT LIKE result (they represent unknown values, so 'not Amul' includes 'unknown brand') and add the IS NULL check accordingly.

Cause: The pattern is broader than intended. 'Tata%' matches any string starting with exactly 'Tata' — including 'TataSteel' (no space). If you intended to match products from the Tata brand where the product name starts with 'Tata ' (with a trailing space), the pattern 'Tata%' without the space matches more than expected. This is a pattern design issue, not a SQL bug.

Fix: Add the space to the pattern to match brand name followed by a product name: WHERE product_name LIKE 'Tata %' (note the space before %). This excludes 'TataSteel' which has no space after 'Tata'. Alternatively, filter on the brand column directly: WHERE brand = 'Tata' — which is more reliable than pattern matching on product_name. Always prefer exact equality on a dedicated column over pattern matching on a combined column when the structure allows it.

Cause: You tried to match a literal percent sign without escaping it. In a LIKE pattern, % always means 'any sequence of characters'. To match a literal percent sign, you must escape it using the ESCAPE clause. Without escaping, LIKE '100%' means '100 followed by anything' — so it matches '100apples', '100%', '100 units', etc., all for the same reason.

Fix: Use the ESCAPE clause to escape the literal %: WHERE value LIKE '100\%' ESCAPE '\'. This tells the database that \% is a literal percent character, not a wildcard. Verify by running SELECT '100%' LIKE '100\%' ESCAPE '\' — this should return TRUE. If you also need to match values starting with '100%' followed by more text: WHERE value LIKE '100\%%' ESCAPE '\' — the first \% is literal, the second % is the wildcard.

The FreshCart marketing team is running a campaign targeting: (1) customers whose email is from a non-Gmail provider AND who live in cities ending with 'abad' or 'abad' variant — specifically Austin or Ahmedabad. (2) Separately, find all products from brands whose name is exactly 5 characters long. Write both queries.