Course contentsShow
← Data Science
Lesson 1046 of 2,14526. Advanced SQLFree lesson

Pattern Matching with LIKE, ILIKE, and SIMILAR TO

Finding patterns in strings using wildcards, case-insensitive matching, and SQL regex-like operators.

Pattern Matching with LIKE, ILIKE, and SIMILAR TO

You'll learn how to search for text patterns using SQL's powerful matching operators for flexible string queries.

The Core Idea

Sometimes you don't know the exact string you're searching for—you just know part of it or its general pattern. SQL's pattern matching operators let you find strings that resemble what you're looking for, not just exact matches. Think of it like using a search engine with wildcards instead of typing an entire phrase.

LIKE: Basic Pattern Matching

LIKE uses two wildcards:

  • % matches any sequence of characters (including zero)
  • _ matches exactly one character
SELECT name 
FROM customers
WHERE email LIKE '%@gmail.com';  -- Ends with @gmail.com

SELECT product_name
FROM products
WHERE product_name LIKE 'Laptop_%';  -- Starts with "Laptop_" + one char

Note: LIKE is case-sensitive in most databases.

ILIKE: Case-Insensitive Matching

ILIKE works exactly like LIKE, but ignores case differences. Perfect when you don't care about capitalization.

SELECT title
FROM articles
WHERE title ILIKE '%data science%';  -- Matches "Data Science", "DATA SCIENCE", etc.

SIMILAR TO: SQL Pattern Matching with Regex-Like Features

SIMILAR TO combines SQL wildcards with limited regular expression syntax. Use | for OR, [] for character classes, and {} for repetition.

SELECT phone
FROM contacts
WHERE phone SIMILAR TO '[0-9]{3}-[0-9]{3}-[0-9]{4}';  -- Matches 123-456-7890

SELECT code
FROM products
WHERE code SIMILAR TO '(A|B)%';  -- Starts with A or B

Key Takeaway: Use LIKE for simple wildcard searches, ILIKE when case doesn't matter, and SIMILAR TO when you need more sophisticated pattern matching with character classes and alternation—all without leaving SQL.