SQL LIKE: Pattern Matching
Pattern matching helps when text values are useful but not perfectly exact.
Exact matches are useful, but text data often needs a softer search.
LIKE
compares a text value to a pattern instead of one exact value.
The most common wildcard is
%, which means any number of characters. So
'%Soil%'
matches text with Soil anywhere inside it. The other common wildcard is
_, which matches exactly one character.
LIKE
matches letter case exactly, so
'%soil%'
finds nothing here: every product spells it
Soil. When case shouldn't matter, DuckDB and PostgreSQL offer
ILIKE, which ignores it.
select column_one, column_two
from table_name
where text_column like '%some text%'Rows that don't contain a word
Put
NOT
in front to keep everything else:
where product_name not like '%Soil%'
returns every product whose name doesn't contain Soil.
NOT ILIKE
does the same while ignoring case.
A missing value is the exception. A
NULL
name matches neither
like '%Soil%'
nor
not like '%Soil%', because both answers are unknown, so that row drops out of both queries.
Add
or product_name is null
when those rows belong in the result.
Lesson 2.8 · True, false, and unknown
explains why.
Schema · Garden Shop Table · products 9 columns · 24 rows
One row per product, with price, cost, and inventory levels.
Show the product name and price for every product whose name contains Soil.
Run your query to inspect the result.
Expected answer
- Columns: product_name, price.
- Rows: 2 products.
- Both product names should contain Soil.
That's the last lesson in “Filtering in Depth.”
The next chapter builds on what this one covered.
Start Chapter 4: Aggregations and Grouping → Begins with 4.1 Counting rows with COUNTRelated
Comparisons, AND/OR, IN, BETWEEN, and LIKE.
SELECT, WHERE, ORDER BY, and LIMIT at a glance.
Debug filters that match nothing.
Nested SELECTs, IN, EXISTS, and correlation.
UNION, UNION ALL, INTERSECT, and EXCEPT.
A stray NULL drops every row. Use NOT EXISTS.