SR

LIKE & Pattern Matching

Search text with wildcard patterns — partial matches, prefixes, suffixes

Beginner 10 minLIKEwildcardspattern

Pattern Matching with LIKE

LIKE matches text against a pattern using two wildcard characters. It's how you search for partial matches, prefixes, suffixes, and patterns within text columns. Here's the syntax:

`%` matches any characters (zero or more) — LIKE '%desk%' matches "Standing Desk", "Desk Lamp", or anything containing "desk" anywhere. LIKE 'A%' matches strings starting with A. LIKE '%son' matches strings ending with "son".
`_` matches exactly one character — LIKE 'J___' matches any 4-character string starting with J. Each underscore represents precisely one character position — useful for fixed-length pattern matching.
Case sensitivity varies — LIKE is case-insensitive in SQLite, but only for ASCII letters. Non-ASCII characters like é vs É or ü vs Ü are treated as different unless SQLite is built with the ICU extension. PostgreSQL is fully case-sensitive — use ILIKE for case-insensitive matching. MySQL's behavior depends on the column collation — _ci collations match case-insensitively, _cs/_bin don't. Always test on your target database.
NOT LIKE excludes matches — WHERE name NOT LIKE '%test%' filters out any rows containing "test" in the name column. Useful for cleaning out test data or excluding specific patterns from results.

Knowledge Canvas

How LIKE Works

Pattern matching for strings

  • % matches zero or more characters
  • _ matches exactly one character
  • LIKE case sensitivity varies: SQLite is case-insensitive for ASCII by default; PostgreSQL is case-sensitive (use ILIKE); MySQL depends on collation
  • NOT LIKE excludes matching patterns

Pattern Cookbook

Common LIKE patterns

  • Starts with: LIKE 'A%' → Alice, Aarav, Admin
  • Ends with: LIKE '%@gmail.com'
  • Contains: LIKE '%phone%' → iPhone, Headphones
  • Exact length: LIKE '___' → 3-char strings only
  • Second char is 'a': LIKE '_a%'

LIKE Pitfalls

Performance and behavior traps

  • Leading %: LIKE '%term' can't use indexes → full scan
  • Escape literal %: LIKE '10\%' or use ESCAPE clause
  • Case sensitivity varies by database and collation
  • LIKE with NULL → NULL (not TRUE or FALSE)

LIKE vs Other Matching

Alternatives to consider

LIKE: simple patterns
REGEXP/SIMILAR TO: complex patterns
% and _ wildcards
Full regex syntax
Portable across databases
Syntax varies by database

Syntax

Syntax Template
1WHERE column LIKE 'pattern';
2
3-- Wildcards
4-- % matches any characters (zero or more)
5-- _ matches exactly one character
6
7-- Examples
8-- WHERE name LIKE 'A%' → starts with A
9-- WHERE name LIKE '%son' → ends with son
10-- WHERE code LIKE 'J___' → 4 chars, starts with J
11
12-- Exclude matches
13-- WHERE name NOT LIKE '%test%'
%Matches zero or more characters
_Matches exactly one character
NOT LIKEExcludes rows matching the pattern

Sample Data

products
10 rows
namecategory
Wireless MouseElectronics
Office DeskFurniture
LED Desk LampFurniture
USB-C CableElectronics
Bluetooth SpeakerElectronics
Office ChairFurniture
Coffee MakerAppliances
Notebook SetStationery
Wireless HeadphonesElectronics
Standing DeskFurniture
customers
12 rows
idnameemailcitycountry
C1001Aarav Sharmaaarav@email.comMumbaiIndia
C1002Sara Chensara@email.comSingaporeSingapore
C1003James Wilsonjames@email.comLondonUK
C1004Maria Garciamaria@email.comMadridSpain
C1005Yuki Tanakayuki@email.comTokyoJapan
C1006Priya Patelpriya@email.comDelhiIndia
C1007Alex Johnsonalex@email.comNew YorkUSA
C1008Chen Weiwei@email.comShanghaiChina
C1009Emma Brownemma@email.comSydneyAustralia
C1010Omar Hassanomar@email.comDubaiUAE
C1011Lena Mullerlena@email.comBerlinGermany
C1012Ravi Kumarravi@email.comBangaloreIndia

Worked Example

Find all products whose name contains 'Desk'.

SQL
1SELECT
2 name,
3 category
4FROM products
5WHERE name LIKE '%Desk%';
The % on both sides means 'Desk' can appear anywhere in the name — at the start, middle, or end. 'Office Desk', 'LED Desk Lamp', and 'Standing Desk' all match. 'Wireless Mouse' does not contain 'Desk' so it is excluded.
Output
3 rows
namecategory
Office DeskFurniture
LED Desk LampFurniture
Standing DeskFurniture

Key Concepts

1% = any characters (zero or more), _ = exactly one character
2LIKE case-folding is database-specific — SQLite folds only ASCII; PostgreSQL is case-sensitive (use ILIKE); MySQL depends on collation
3Searching for literal % or _ requires an ESCAPE clause
4'%keyword%' scans every row — can be slow on large tables

Pro Tip

Not all searches are exact matches. LIKE lets you find partial matches, prefixes, suffixes, and text that follows a structure.

When to Use

Searching by partial name, finding products with a keyword, matching email domains, validating text formats.

Challenge

Solve the problem below

Find all customers whose email ends with '@email.com'. Show name and email, ordered by name.

Your Query

Previous

BETWEEN

Next

CASE WHEN