LIKE (pattern matching)
Matches text using wildcard patterns (% and _).
SELECT columns FROM table_name WHERE column LIKE pattern;This static page keeps the syntax and examples indexed for search, while the coding app handles interactive exploration and saved references.
What it does
Overview
Matches text using wildcard patterns (% and _).
The LIKE operator is a fundamental tool for string pattern matching in SQL, primarily used within WHERE clauses to filter results. It utilizes two reserved wildcard characters: the percent sign (%) representing zero, one, or multiple characters, and the underscore (_) representing exactly one single character. A critical technical consideration is query performance; LIKE operations are only 'SARGable' (Search Argumentable) when the wildcard is not placed at the beginning of the pattern. A leading wildcard (e.g., '%term') forces the database engine to perform a full table scan because it cannot utilize a B-tree index to locate the start of the string. Case sensitivity is not universal; it is determined by the specific database engine and the collation settings of the column (for instance, PostgreSQL is case-sensitive by default while SQL Server often uses case-insensitive collations). To match literal wildcards, the ESCAPE clause must be explicitly defined in the query.
Quick reference
Syntax
SELECT columns FROM table_name WHERE column LIKE pattern;Inputs
Parameters
See it in practice
Examples
Basic Prefix Matching
SELECT product_name FROM inventory WHERE product_name LIKE 'Data%';Database Data-cable Dataphone
Retrieves all products where the name begins with the string 'Data'. This query is performance-efficient as it can utilize indexes.
Fixed Length Pattern Matching
SELECT user_id FROM accounts WHERE user_id LIKE 'USR-___';USR-101 USR-A2B USR-999
Uses the underscore wildcard to find IDs that start with 'USR-' followed by exactly three characters of any type.
Escaping Literal Wildcards
SELECT discount_code FROM promotions WHERE discount_code LIKE '10!%%' ESCAPE '!';10% 10%OFF
Demonstrates the ESCAPE clause. The '!' character is used to treat the following '%' as a literal character rather than a wildcard.
Debug faster
Common Errors
Performance Anti-pattern
Cause: Using a leading wildcard (e.g., '%keyword%') prevents the database from using indexes.
Fix: If high performance is required on large text fields, use Full-Text Search (FTS) indexes or avoid leading wildcards.
SELECT * FROM logs WHERE message LIKE '%error%';Case Sensitivity Logic Error
Cause: Assuming LIKE is case-insensitive in systems like PostgreSQL, where it is actually case-sensitive.
Fix: Use the ILIKE operator in PostgreSQL or wrap the column and pattern in UPPER() functions for cross-platform consistency.
SELECT * FROM users WHERE username LIKE 'admin'; -- Fails if value is 'Admin'Runtime support
Compatibility
Case sensitivity varies by collation/DB
Source: MDN Web Docs
Common questions
Frequently Asked Questions
Matches text using wildcard patterns (% and _).
pattern: Pattern like 'Sam%' or '%@gmail.com'.
Performance Anti-pattern: If high performance is required on large text fields, use Full-Text Search (FTS) indexes or avoid leading wildcards. Case Sensitivity Logic Error: Use the ILIKE operator in PostgreSQL or wrap the column and pattern in UPPER() functions for cross-platform consistency.