0Pricing
SQL Academy · Lesson

LIKE Patterns and Wildcards

Use LIKE with % and _ wildcards, understand case sensitivity, and prefer ILIKE on PostgreSQL for case-insensitive matching.

LIKE Patterns and Wildcards is a free SQL Academy lesson on CoddyKit — lesson 1 of 4. You can read the complete lesson below for free — then practise it hands-on in the browser with a built-in code editor and a 24/7 AI tutor. It is part of the SQL Academy learning path, one of 4 lessons in the course, and your progress syncs across the web and the CoddyKit app.

What LIKE Does

LIKE matches a string against a pattern. Two wildcards:

  • % — any sequence (including empty)
  • _ — exactly one character

Simple Examples

Match by prefix, suffix and substring:

SELECT email FROM users WHERE email LIKE 'a%';        -- starts with a
SELECT email FROM users WHERE email LIKE '%@gmail.com'; -- ends with @gmail.com
SELECT email FROM users WHERE email LIKE '%admin%';     -- contains admin

Single-Character Wildcards

_ matches exactly one character. Great for fixed-width codes:

-- 6-char SKU starting with A:
SELECT * FROM products WHERE sku LIKE 'A_____';

-- US zip+4:
SELECT * FROM addresses WHERE zip LIKE '_____-____';

Case Sensitivity

LIKE is case-sensitive by default in PostgreSQL. Use ILIKE for case-insensitive matching:

SELECT * FROM users WHERE email LIKE  '%@gmail.com';  -- ✗ misses @GMAIL.com
SELECT * FROM users WHERE email ILIKE '%@gmail.com';  -- ✓

Escaping the Wildcards

To match a literal % or _, escape with backslash (or define your own ESCAPE char):

-- Match strings that contain '50%':
SELECT * FROM coupons WHERE code LIKE '%50\%%';

-- Custom escape character:
SELECT * FROM coupons WHERE code LIKE '%50!%%' ESCAPE '!';

LIKE and Indexes

A B-tree index helps with prefix patterns only:

-- Uses index (prefix is fixed):
SELECT * FROM users WHERE email LIKE 'alice%';

-- Does NOT use a normal index (leading wildcard):
SELECT * FROM users WHERE email LIKE '%example.com';

Fixing Leading-Wildcard Searches

For "%example%" searches, use a trigram (pg_trgm) GIN index — covered in Advanced Indexing.

CREATE EXTENSION IF NOT EXISTS pg_trgm;
CREATE INDEX users_email_trgm_idx ON users USING GIN (email gin_trgm_ops);

SELECT * FROM users WHERE email LIKE '%example%';  -- now indexable

NOT LIKE

Negate the match:

SELECT * FROM emails WHERE subject NOT LIKE 'RE:%';

SIMILAR TO and Regex (PostgreSQL)

For richer pattern matching:

-- SIMILAR TO — SQL regex (rarely used)
SELECT 'abc' SIMILAR TO '%(a|b)%';      -- TRUE

-- POSIX regex:
SELECT 'abc123' ~ '^[a-z]+[0-9]+$';     -- TRUE
SELECT 'ABC'    ~* 'abc';                -- TRUE (case insensitive)

Building Search Boxes

When wiring up a user search box, sanitize wildcards in the input first to avoid surprising behaviour:

-- Escape user input's special chars before concatenating to LIKE pattern,
-- or use to_tsquery for word-level search.

SELECT * FROM products
WHERE LOWER(name) LIKE LOWER('%' || $1 || '%');

Don't Reinvent Full-Text Search

For ranked, language-aware search across documents, use tsvector + GIN, not LIKE. Covered later in Advanced Indexing.

Recap

LIKE is great for simple pattern matching.

  • % = any string, _ = one char
  • Use ILIKE for case-insensitive
  • Prefix searches use B-tree; substring needs trigram

Quick Check

Which pattern matches strings ending in .com?

Frequently asked questions

Is the “LIKE Patterns and Wildcards” lesson free?

Yes — the full text of “LIKE Patterns and Wildcards” is free to read here on the web, and the SQL Academy course includes 4 lessons in total. To practise it interactively (a built-in code editor and a 24/7 AI tutor) and unlock the rest of the SQL Academy course, upgrade to CoddyKit PRO.

What will I learn in “LIKE Patterns and Wildcards”?

Use LIKE with % and _ wildcards, understand case sensitivity, and prefer ILIKE on PostgreSQL for case-insensitive matching. You practise SQL Academy with hands-on code you run directly in the browser, and a 24/7 AI tutor answers your questions as you work through the lesson.

Do I need any experience to start SQL Academy?

No prior experience is required. SQL Academy on CoddyKit is structured for beginners through advanced learners; this is — lesson 1 of 4, so you can start here or from the beginning and move at your own pace.

How long does the “LIKE Patterns and Wildcards” lesson take?

Most CoddyKit lessons take about 5–10 minutes. Each one is bite-sized and interactive, so you make steady progress and pick up exactly where you left off across the web and the app.

Can I write and run code in this SQL Academy lesson?

Yes. Every SQL Academy lesson includes a built-in code editor, so you write and run real code right in your browser and get instant AI feedback — no local setup required.

All lessons in this course

  1. LIKE Patterns and Wildcards
  2. IN and NOT IN for Sets
  3. BETWEEN for Ranges
  4. IS NULL, IS NOT NULL and COALESCE
← Back to SQL Academy