🔍LIKE patterns

LIKE is not full-text search

LIKE does simple character-pattern matching with % (any length) and _ (single character). It respects collation rules, case and accent sensitivity can flip between “works on my laptop” and “fails in prod.” For product search boxes on big tables, plan trigram or FTS; use LIKE for controlled prefixes (accessions, instrument IDs).

SQL

Prefix search (index-friendly when column is indexed)

SELECT accession
FROM sequences
WHERE accession LIKE 'NM_%';

✓Illustrative result set

accession
NM_007294
NM_000546
NM_005228

SQL

Single-character mask

SELECT clone_id
FROM clones
WHERE clone_id LIKE 'C-2026-_A';

✓Illustrative result set

clone_id
C-2026-3A
C-2026-9A

📋Pattern cookbook

Wildcards lesson covers escaping and literal % / _ .

Pattern	Meaning
starts%	Values beginning with “starts”
%ends	Values ending with “ends”
%contains%	Substring, usually hardest on B-trees

Bind patterns, not paste user text

Build the pattern in application code after escaping % and _ from user input, never let raw substrings become pattern metacharacters by accident.

Collation

Document whether search is case-insensitive and whether you rely on ILIKE (Postgres) vs LOWER(col) (portable but can block indexes).

Performance smell

If explain plans show sequential scans on millions of rows for '%needle%', LIKE is the wrong tool, graduate the feature to FTS or an external search index.

TL;DR

LIKE matches string patterns: % matches any substring, _ matches one character (escape with vendor-specific ESCAPE clauses). Leading wildcards ('%foo') usually prevent B-tree index use, expect scans. Case sensitivity follows the column collation; for user search at scale use full-text engines or external indexes, not raw LIKE on huge heaps.