The Project | About Us | Contribute | Donations | License
HOME My courses Completed
◄ Previous
◄

Using LIKE for Pattern Matching

Next ►
►

When module 3 closed, two halves were left pending: sharpening the filtering and learning to aggregate. This lesson opens the first. Up to now, whenever you've filtered text you've done it with =, that is, demanding an exact, character-by-character match. That works for country = 'Portugal', where the value is a closed code, but it's no use at all for anything a business really asks: "the products carrying the word organic", "the customers with a Portuguese email", "the job titles that end in manager".

That's what LIKE exists for: an operator that compares a string against a pattern instead of against a value. In this lesson you'll learn its two wildcards, the case trap and its three solutions, how to search for a % that's a genuine percentage and not a wildcard, PostgreSQL's regular expressions for the cases LIKE doesn't cover, and —very importantly— why a search that begins with a wildcard can wreck performance on a large table.

Contents

  1. From exact equality to patterns
  2. LIKE and NOT LIKE: syntax and wildcards
  3. Pattern table: what matches and what doesn't
  4. Examples over GreenStore
  5. Upper and lower case: ILIKE, LOWER and collations
  6. Searching for a literal % or _: ESCAPE
  7. SIMILAR TO and regular expressions
  8. Performance: why '%text%' can't use an index
  9. Common Mistakes and Tips
  10. Exercises
  11. Conclusion

  1. From exact equality to patterns

The problem with = is that it admits no shades. If management asks for "the listing of organic products", WHERE name = 'Organic' returns zero rows: no product is called exactly that, the word is inside the name.

SELECT id, name
FROM products
WHERE name = 'Organic';
(0 rows)

LIKE solves this by comparing against a pattern, where certain characters stop standing for themselves and start meaning "anything at all".

SELECT id,
       name,
       category_id,
       price
FROM products
WHERE name LIKE '%Organic%'
ORDER BY id;
id name category_id price
2 Organic brown rice 1 kg 1 3.90
5 Organic crushed tomato 400 g 1 1.95
14 Organic chamomile tea 20 bags 4 3.25

3 rows. Notice two details of the pattern. First, the capital O: that's exactly how the word appears in the catalogue, and LIKE is case-sensitive in PostgreSQL (section 5 gives you three ways round that). Second, the pattern is deliberately short: a well-chosen fragment is enough, and cutting the pattern before the part of a word that varies is an elementary and effective trick. '%bag%', for instance, matches both Reusable cotton bags and Organic chamomile tea 20 bags, singular or plural.

  1. LIKE and NOT LIKE: syntax and wildcards

The general form is:

<text expression> [NOT] LIKE '<pattern>' [ESCAPE '<character>']

The pattern is an ordinary string in which two characters have a special meaning:

Wildcard Meaning Analogy
% Any sequence of zero or more characters The * of operating-system filenames
_ Exactly one character, whatever it is The ? of filenames

Any other character in the pattern is compared literally. And there's a consequence that surprises a lot of people: a pattern with no wildcards is equivalent to =.

-- These two conditions are equivalent
WHERE country LIKE 'Portugal'
WHERE country =    'Portugal'

LIKE's result is a boolean, so it can be combined with AND, OR, NOT and parentheses exactly like the comparisons from 02-03. NOT LIKE is its negation:

SELECT id,
       name,
       last_name,
       job_title
FROM employees
WHERE job_title NOT LIKE '%manager'
ORDER BY id;
id name last_name job_title
4 Óscar Peris Blasco Sales rep
5 Laia Puig Sanchis Sales rep
6 Marc Estévez Roig Customer support
7 Irene Salvador Mira Warehouse operator
8 Daniel Vercher Lluch Data analyst

5 rows out of 8. Left out are Rosa Alcázar Vives (General manager), Andrés Company Talens (Sales manager) and Beatriz Nadal Ripoll (Logistics manager).

A null warning, right now: NOT LIKE drags along the same problem as <> in 02-03. If the column allows NULL, those rows don't appear with LIKE or with NOT LIKE, because the comparison returns unknown in both cases. Here job_title is NOT NULL and there's no risk, but with customers.city there would be. Lesson 04-03 explains it in depth.

  1. Pattern table: what matches and what doesn't

The four canonical patterns, with the name each one gets in practice:

Pattern Name Matches Doesn't match
'Organic%' Prefix (starts with) Organic brown rice…, Organic crushed tomato… Almond body oil…, organic… (lower case)
'%ml' Suffix (ends in) …extra 500 ml …20 bags, …1 kg
'%vera%' Contains Aloe vera face cream 50 ml Rosemary solid shampoo 80 g
'____' Exact length (4 characters) Rosa, Marc, Laia Óscar (5), Ana (3)

And the ones that mix both wildcards:

Pattern Matches Comment
'B%a' Barcelona (in customers.city) Starts with B and ends in a; it doesn't match Bosch Ferrer
'%(pack of _)' …scrubber (pack of 3), …bags (pack of 5), …candles (pack of 2) The _ stands in for the digit
'A_a' Ana Three letters, first A, last a

Let's check the second one from that table against real data:

SELECT id, name, price
FROM products
WHERE name LIKE '%(pack of _)'
ORDER BY id;
id name price
11 Loofah scrubber (pack of 3) 5.50
12 Reusable cotton bags (pack of 5) 9.90
13 Soy wax candles (pack of 2) 13.75

3 rows, the three products sold in multipack format. The _ has matched 3, 5 and 2 respectively. If we'd written '%(pack of %)' we'd have got the same thing, but (pack of 12) or (pack of assorted) would match too: _ is stricter and therefore more precise when you know a single character goes there.

The _ wildcard is easy to forget

An instructive case with orders.payment_method:

SELECT DISTINCT payment_method
FROM orders
WHERE payment_method LIKE 'cash_on_delivery';
payment_method
cash_on_delivery

It matches. But not because the underscores in the pattern are underscores: each _ matched whatever character sat in that position, which here happens to be an underscore. The very same pattern would also match cash-on-delivery or cashXonYdelivery. It's the mistake that makes WHERE column LIKE 'unit_price' find things you weren't expecting when you're searching for column names in a metadata catalogue. Section 6 shows you how to avoid it.

  1. Examples over GreenStore

4.1. Products by format: the suffix trap

The catalogue encodes the format at the end of the name. Small liquid containers end in ml:

SELECT id,
       name,
       category_id,
       price
FROM products
WHERE name LIKE '%ml'
ORDER BY id;
id name category_id price
1 Extra virgin olive oil 500 ml 1 12.50
6 Aloe vera face cream 50 ml 2 18.90
8 Almond body oil 200 ml 2 14.25
9 Calendula lip balm 15 ml 2 4.60
16 Ginger kombucha 750 ml 4 4.95

5 rows. Now the ones sold by weight in grams:

-- ⚠️ INCORRECT: '%g' also matches 'kg'
SELECT id, name
FROM products
WHERE name LIKE '%g'
ORDER BY id;
id name
2 Organic brown rice 1 kg
3 Raw orange blossom honey 500 g
4 Spelt pasta 500 g
5 Organic crushed tomato 400 g
7 Rosemary solid shampoo 80 g
15 Ceremonial matcha green tea 30 g
19 Natural stick deodorant 50 g

7 rows, and one of them shouldn't be there: the rice is sold in kilos, not grams. '%g' means "ends in the letter g", and kg ends in g. The fix is to include the space in the pattern:

-- ✅ CORRECT
SELECT id, name, price
FROM products
WHERE name LIKE '% g'
ORDER BY id;
id name price
3 Raw orange blossom honey 500 g 9.75
4 Spelt pasta 500 g 2.80
5 Organic crushed tomato 400 g 1.95
7 Rosemary solid shampoo 80 g 8.40
15 Ceremonial matcha green tea 30 g 22.00
19 Natural stick deodorant 50 g 7.80

6 rows. The space in front of the g is the difference between a correct report and one that mixes units. It's exactly the kind of mistake that gives no warning at all.

4.2. Customers by email domain

GreenStore sells to three countries and the sample emails reflect each one's domain. The Portuguese customers:

SELECT id,
       name || ' ' || last_name AS customer,
       email,
       city,
       country
FROM customers
WHERE email LIKE '%.pt'
ORDER BY id;
id customer email city country
7 Sofia Moreira Costa [email protected] Lisbon Portugal
8 Tiago Almeida Nunes [email protected] Porto Portugal

And the French ones, combining two patterns with OR:

SELECT id,
       name || ' ' || last_name AS customer,
       email,
       country
FROM customers
WHERE email LIKE '%.fr'
   OR email LIKE '%.pt'
ORDER BY country, id;
id customer email country
9 Camille Dubois [email protected] France
10 Julien Moreau [email protected] France
7 Sofia Moreira Costa [email protected] Portugal
8 Tiago Almeida Nunes [email protected] Portugal

4 rows: the four international customers. The other 11 use example.com.

Watch what you're actually measuring. Filtering by the email domain is not the same as filtering by country. They coincide here because the data set was built that way, but in a real database there are Portuguese people with @gmail.com and Spaniards with @example.fr. If the business question is "customers from Portugal", the correct answer is WHERE country = 'Portugal', not a LIKE over the email. LIKE is powerful, and that's exactly why it's easy to use it to answer a similar but different question.

4.3. Job titles in the org chart

SELECT id,
       name || ' ' || last_name AS employee,
       job_title,
       salary
FROM employees
WHERE job_title LIKE '%manager'
ORDER BY id;
id employee job_title salary
1 Rosa Alcázar Vives General manager 62000.00
2 Andrés Company Talens Sales manager 41000.00
3 Beatriz Nadal Ripoll Logistics manager 39500.00

3 rows: everyone with management responsibility in the hierarchy you saw in 03-06 — the general manager and the two area managers.

Notice the shape of the pattern. In English the management roles end in manager rather than starting with a common prefix, so this is a suffix pattern ('%manager') and not a prefix one. That has a very practical consequence you'll see in section 8: a suffix pattern can't take advantage of an index, while a prefix pattern —'Sales%', for instance, which would bring back the Sales manager and the two Sales reps— can. When you get to design searches over large tables, whether the fixed part of the pattern sits at the beginning or at the end stops being a stylistic question.

  1. Upper and lower case: ILIKE, LOWER and collations

In 02-03 you saw that country = 'spain' returns zero rows. LIKE inherits exactly the same behaviour: in PostgreSQL, LIKE is case-sensitive.

SELECT id, name FROM products WHERE name LIKE 'organic%';
(0 rows)
SELECT id, name FROM products WHERE name LIKE 'Organic%';
id name
2 Organic brown rice 1 kg
5 Organic crushed tomato 400 g
14 Organic chamomile tea 20 bags

And this is a real problem, because whoever types into a search box doesn't reach for the shift key. There are three solutions.

Solution 1: ILIKE (a PostgreSQL extension)

SELECT id, name, price
FROM products
WHERE name ILIKE '%organic%'
ORDER BY id;
id name price
2 Organic brown rice 1 kg 3.90
5 Organic crushed tomato 400 g 1.95
14 Organic chamomile tea 20 bags 3.25

The I is for insensitive. Its negation is NOT ILIKE. It's the most readable option and the one we'll use in the course when working on PostgreSQL, with one caveat: it isn't standard SQL, so a query with ILIKE isn't portable.

Solution 2: LOWER on both sides

SELECT id, name, price
FROM products
WHERE LOWER(name) LIKE LOWER('%Organic%')
ORDER BY id;

It returns the same 3 rows. It's portable to any engine, which is why it's the form you'll see in code that has to work on several databases.

Two important details:

  • LOWER goes on both sides. If you write LOWER(name) LIKE '%Organic%' nothing will ever match, because the column has been lowercased but the pattern hasn't.
  • Applying a function to the column prevents a normal index from being used. It's the same warning from 02-03 about WHERE price - cost > 5. The solution (an index on the expression LOWER(name)) is module 8 material.

LOWER and UPPER are studied in depth, along with the rest of the string functions, in lesson 06-01. Here we're only using them as a tool.

Solution 3: a case-insensitive collation

PostgreSQL 12 and later let you define non-deterministic collations, which make the comparison itself ignore case (and even accents). They're declared once and affect =, LIKE and ORDER BY without touching the queries:

CREATE COLLATION en_ci (
    provider = icu,
    locale   = 'en-US-u-ks-level2',
    deterministic = false
);

It's the cleanest solution when an entire column has to be compared that way, but it's a schema design decision (module 5) and it has trade-offs: with a non-deterministic collation, LIKE over that column can't use the usual indexes.

Behaviour by engine

Engine Is LIKE case-sensitive? Idiomatic insensitive form
PostgreSQL Yes, always ILIKE, or LOWER(col) LIKE LOWER(pat), or a non-deterministic collation
MySQL / MariaDB No by default: it depends on the collation, and the usual one (utf8mb4_0900_ai_ci) is insensitive to case and to accents It already is; to make it sensitive, LIKE ... COLLATE utf8mb4_0900_as_cs or BINARY
SQLite No for ASCII, yes for everything else: 'a' LIKE 'A' is true, but 'á' LIKE 'Á' is false LOWER(col) LIKE LOWER(pat), with the same ASCII limitation
SQL Server It depends on the column's collation; typical installations are insensitive (_CI_) LOWER(...) or an explicit COLLATE ..._CI_AS
Oracle Yes LOWER(...), or the session parameters NLS_COMP/NLS_SORT

Practical consequence: a LIKE query that works perfectly in MySQL can return zero rows when ported to PostgreSQL, with nothing having changed in the data. It's one of the dialect differences that wastes the most time in migrations. When you write SQL that has to travel, use LOWER() on both sides and don't rely on anybody's default behaviour.

  1. Searching for a literal % or _: ESCAPE

And what if what you're looking for is a percent sign? Since % means "anything", the pattern '%%%' doesn't search for a percentage: it matches absolutely everything.

The solution is to escape the wildcard, marking it with a character that strips its power. In PostgreSQL (and in MySQL) the default escape character is the backslash \:

SELECT message
FROM (VALUES ('20% off drinks'),
             ('Free shipping over 50 EUR'),
             ('2-for-1 pack on cosmetics')) AS t(message)
WHERE message LIKE '%\%%';
message
20% off drinks

Read the pattern '%\%%' from left to right: % (anything) + \% (a literal percent sign) + % (anything).

If the backslash strikes you as unreadable —or if your data contains backslashes— the ESCAPE clause lets you choose another character:

SELECT message
FROM (VALUES ('20% off drinks'),
             ('Free shipping over 50 EUR'),
             ('2-for-1 pack on cosmetics')) AS t(message)
WHERE message LIKE '%!%%' ESCAPE '!';
message
20% off drinks

The same result, a more readable pattern. ESCAPE works the same way for the underscore. Coming back to section 3's case, this is how you search for a genuine _:

Query What it really searches for
LIKE 'cash_on_delivery' cash + any character + on + any character + delivery → matches cash_on_delivery, but also cash-on-delivery
LIKE 'cash\_on\_delivery' The literal string cash_on_delivery, underscores included
LIKE 'unit\_price' The exact column name, with its underscore

Dialect note: the SQL standard doesn't define a default escape character; it requires you to declare one with ESCAPE. PostgreSQL and MySQL do have one (\); SQL Server and Oracle don't, so there LIKE '%\%%' literally searches for a backslash followed by anything and you have to write ESCAPE '\' explicitly. Always writing an explicit ESCAPE is the portable option.

  1. SIMILAR TO and regular expressions

LIKE falls short as soon as the question involves alternatives ("ends in ml, g, kg, L, units or bags"), repetitions ("two or more digits") or character classes ("a letter followed by a number"). PostgreSQL offers two more families.

7.1. SIMILAR TO

It's standard SQL and it's a hybrid: it uses LIKE's wildcards (% and _) plus some regular-expression operators (|, *, +, ?, (), [], {}).

SELECT id, name
FROM products
WHERE name SIMILAR TO '%(ml|kg)'
ORDER BY id;
id name
1 Extra virgin olive oil 500 ml
2 Organic brown rice 1 kg
6 Aloe vera face cream 50 ml
8 Almond body oil 200 ml
9 Calendula lip balm 15 ml
16 Ginger kombucha 750 ml

6 rows: the 5 products in millilitres plus the rice in kilos. With LIKE you'd have needed two conditions joined by OR.

In practice SIMILAR TO is rarely used: whoever needs that power usually prefers full regular expressions, and whoever doesn't sticks with LIKE. It's worth knowing about because it shows up in legacy code and because it's the only one of the three families that, along with LIKE, is part of the standard.

7.2. POSIX regular expressions: ~, ~*, !~, !~*

These are PostgreSQL's native operators and they use the regular-expression syntax you already know from any programming language:

Operator Meaning
~ Matches the regular expression, case-sensitively
~* Matches, case-insensitively
!~ Does not match, case-sensitively
!~* Does not match, case-insensitively

A fundamental difference from LIKE: a regular expression matches anywhere in the string by default, not from start to end. That's why ^ (start) and $ (end) are needed when you want to anchor it.

Example 1: validating an email address's format.

SELECT id,
       name || ' ' || last_name AS customer,
       email
FROM customers
WHERE email !~ '^[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Za-z]{2,}$'
ORDER BY id;
(0 rows)

Zero rows, which here is the desired answer: all 15 GreenStore emails have a valid format. This pattern —searching for what doesn't comply— is the usual way of auditing data quality before a migration or an email campaign.

Example 2: products whose name ends in a unit of measurement.

SELECT id,
       name,
       price
FROM products
WHERE name !~ '[0-9]+ ?(ml|kg|g|L|units|bags)$'
ORDER BY id;
id name price
11 Loofah scrubber (pack of 3) 5.50
12 Reusable cotton bags (pack of 5) 9.90
13 Soy wax candles (pack of 2) 13.75
18 Bamboo toothbrush 3.50

4 rows: the only four products in the catalogue that don't state a size at the end. The other 16 do. This is a genuine catalogue quality check: if tomorrow the content team adds a reference with no format, this query gives it away. With LIKE you'd have to chain six conditions with OR and even then you couldn't demand that a number sat in front of the unit.

7.3. The three families compared

LIKE / ILIKE SIMILAR TO ~ (POSIX)
Standard SQL Yes Yes No (PostgreSQL)
Wildcards %, _ %, _ + regex operators Pure regex syntax (., *, +, ?, [], (), |)
Anchoring Always to the whole string Always to the whole string No, unless you use ^ and $
Alternatives (a or b) No Yes, (a|b) Yes, a|b
Repetitions No Yes, {2,4} Yes, {2,4}
Case-insensitive ILIKE Not directly ~*
Can use a B-tree index Yes, if the pattern starts with fixed text No Only with ^ anchoring and planner analysis
Readability Very high Medium Low for anyone who isn't fluent in regex
When to use it 90 % of cases Almost never Complex validations and extractions

Dialect note: regular expressions vary a lot between engines. MySQL uses REGEXP / RLIKE (and REGEXP_LIKE since 8.0), Oracle uses REGEXP_LIKE, and SQLite has the REGEXP operator but the function that implements it isn't bundled in: you have to register it from the application. SIMILAR TO practically only exists in PostgreSQL. If your SQL has to be portable, LIKE is the only safe bet.

  1. Performance: why '%text%' can't use an index

This is the point that separates a query that works over 20 rows from one that works over 20 million.

A B-tree index —the normal index, the one you'll study in module 8— stores values sorted alphabetically, like a dictionary. And that determines what it can and can't speed up:

flowchart TD
    A["LIKE 'Organic%'"] --> B["The index is sorted.<br/>Everything starting with 'Organic'<br/>sits together, in one contiguous stretch"]
    B --> C["✅ Jump straight to that stretch.<br/>Index used"]
    D["LIKE '%organic%'"] --> E["Anything containing 'organic' in the middle<br/>can sit at any point<br/>of the alphabetical order"]
    E --> F["❌ You have to read and check<br/>EVERY row: a full scan"]

Think of it with a paper dictionary: finding all the words that start with organic means opening at O and reading one stretch. Finding all the ones that contain organic forces you to read the whole dictionary.

Pattern Type B-tree index?
LIKE 'Organic%' Fixed prefix Yes
LIKE 'Organic%rice%' Fixed prefix + rest Yes, it uses the prefix to narrow down
LIKE '%organic' Suffix No
LIKE '%organic%' Contains No
LIKE '_rganic%' Starts with a wildcard No
ILIKE 'Organic%' Case-insensitive No with a normal index
LOWER(name) LIKE 'organic%' Function over the column No with a normal index, yes with an index on the expression

There are two nuances worth knowing from now on:

  1. For LIKE 'prefix%' to use the index in PostgreSQL, the index has to use a special operator class (text_pattern_ops) unless the database is in the C collation. It's a detail explained in module 8, but it explains why sometimes "I have the index and it isn't using it".
  2. For '%text%' searches there's a specific solution: the pg_trgm extension, which breaks the text into trigrams (groups of three characters) and lets you create GIN or GiST indexes capable of speeding up LIKE '%text%' and ILIKE '%text%'. It's enabled with CREATE EXTENSION pg_trgm; and it's also covered in module 8.

And when LIKE stops being the tool

LIKE compares characters, not words. It doesn't know that candle and candles are the same word, it doesn't ignore accents, it doesn't rank results by relevance and it doesn't understand that somebody searching for "olive oil" wants the same rows as somebody searching for "oil olive".

That's what full-text search exists for, which PostgreSQL ships with out of the box through the tsvector and tsquery types and the @@ operator. It turns text into a list of lexical roots, ignores stop words and supports very efficient GIN indexes. It's outside this course, but it's worth knowing it exists: if you catch yourself chaining five ILIKE '%…%' conditions with OR to build a search box, LIKE is no longer the right tool.

Common Mistakes and Tips

  • Using = when you meant LIKE. WHERE name = '%ml' raises no error: it literally searches for the string %ml and returns zero rows.
  • Forgetting that LIKE is case-sensitive in PostgreSQL. Zero rows and no clue as to why. If you're coming from MySQL, it's the first surprise you'll get.
  • Applying LOWER to only one side. LOWER(name) LIKE '%Organic%' never matches anything. Both sides.
  • Writing '%g' when you meant '% g'. The 1 kg rice sneaks into the listing of products sold by the gram. Anchor the pattern with the right separator.
  • Trusting that _ is a literal underscore. It's a wildcard. To search for the character, \_ or ESCAPE.
  • Searching for a % without escaping it. LIKE '%%%' returns every row in the table.
  • Assuming NOT LIKE returns "everything else". Rows with NULL come out on neither side. Check that the two halves add up to the total (04-03).
  • Using LIKE over the email to deduce the country. It answers a similar question, not the same one. Use the column that models the fact.
  • Putting a wildcard at the start on a large table. '%text%' forces a full scan. With 20 rows you won't notice; with 20 million, your query takes minutes.
  • Tip: always start with the loosest pattern and tighten it up. Fire off ILIKE '%organ%', look at what comes out, and only then refine. It's quicker than guessing the exact pattern first time.
  • Tip: count the rows of the condition and of its negation. If LIKE gives 6 and NOT LIKE gives 14 over a table of 20, the logic closes. If it doesn't close, there are nulls.
  • Tip: if the pattern has to be portable, write LOWER(col) LIKE LOWER(pat) ESCAPE '\'. It's more verbose, but it works identically on all five engines.

Exercises

Exercise 1

The content team wants to review how the catalogue is written. Write three queries over products:

  1. The products whose name contains the word natural (in any position and regardless of case).
  2. The products whose name starts with the letter C.
  3. The products that don't carry the word Organic in the name but do belong to category 1 (Food).

In each case state how many rows come out and why you chose that pattern.

Exercise 2

Marketing is preparing an email campaign and needs to segment. Over customers:

  1. Count how many customers have an email in the example.com domain, using LIKE.
  2. Write the query that returns the customers whose first name has exactly 3 letters.
  3. A colleague proposes WHERE email LIKE '%@example.com%' for the first part. Does it return the same thing? Is it equivalent? Which would you prefer, and why?

Exercise 3

Management wants a catalogue audit. Write a single query that returns, for each product, its id, its name and a format column holding:

  • the name itself if it does not end in a unit of measurement (the four you saw in section 7.2),
  • and nothing else: only those four products should appear.

Solve it first with regular expressions and then answer: could you have done it with LIKE alone? What would you be missing?

Solutions

Solution 1

1. Contains natural, regardless of case:

SELECT id, name, category_id, price
FROM products
WHERE name ILIKE '%natural%'
ORDER BY id;
id name category_id price
19 Natural stick deodorant 50 g 5 7.80

1 row. ILIKE is used because the word opens the name with a capital N: LIKE '%natural%' in lower case would return zero rows, and LIKE '%Natural%' would work today but would be fragile against a future reference where the word appeared mid-name in lower case.

2. Starts with C:

SELECT id, name, price
FROM products
WHERE name LIKE 'C%'
ORDER BY id;
id name price
9 Calendula lip balm 15 ml 4.60
10 Concentrated eco laundry detergent 1 L 11.20
15 Ceremonial matcha green tea 30 g 22.00
17 Cold-pressed orange juice 1 L 5.40

4 rows. Here LIKE is preferable to ILIKE: product names start with a capital by convention, and ILIKE 'c%' would add nothing while preventing an index from being used.

3. Food without the word Organic:

SELECT id, name, category_id, price
FROM products
WHERE category_id = 1
  AND name NOT LIKE '%Organic%'
ORDER BY id;
id name category_id price
1 Extra virgin olive oil 500 ml 1 12.50
3 Raw orange blossom honey 500 g 1 9.75
4 Spelt pasta 500 g 1 2.80

3 rows. The consistency check: category 1 has 5 products, two of them (2 and 5) carry Organic in the name, and 5 − 2 = 3. It closes. That it closes matters precisely because name is NOT NULL; if it allowed nulls, NOT LIKE would have discarded them and the arithmetic wouldn't add up.

Solution 2

1.

SELECT COUNT(*) AS com_domain_customers
FROM customers
WHERE email LIKE '%@example.com';
com_domain_customers
11

11 customers, the 15 minus the 2 Portuguese and the 2 French ones.

2. A first name of exactly 3 letters: three underscores and nothing else.

SELECT id, name, last_name, city
FROM customers
WHERE name LIKE '___'
ORDER BY id;
id name last_name city
5 Ana Belmonte Roca Barcelona
6 Pau Llorens Vidal Valencia

2 rows. With LIKE '___' (three _) you demand exactly three characters, because the pattern is anchored to the whole string. If you'd written '___%' you'd get every name of three letters or more, that is, almost the whole table.

3. '%@example.com%' returns the same thing here (11 rows), but it isn't equivalent. The trailing % means "and then anything", so it would also match [email protected] or [email protected]. It's a looser pattern that answers "the email contains @example.com", not "the email ends in @example.com".

'%@example.com' is preferable: it says exactly what we want, it drags in no false positives and —no minor detail— a pattern that ends without a wildcard still can't use an index, but at least it doesn't invite logical errors. The general rule: don't put in wildcards you don't need, each one silently widens the set of answers.

Solution 3

SELECT id,
       name AS format
FROM products
WHERE name !~ '[0-9]+ ?(ml|kg|g|L|units|bags)$'
ORDER BY id;
id format
11 Loofah scrubber (pack of 3)
12 Reusable cotton bags (pack of 5)
13 Soy wax candles (pack of 2)
18 Bamboo toothbrush

4 rows. A breakdown of the pattern:

Fragment Meaning
[0-9]+ One or more digits
? An optional space
(ml|kg|g|L|units|bags) Any of the six units
$ Anchored to the end of the string
!~ Returns the rows that do not match

Could you do it with LIKE? Only halfway. You could write:

-- An approximation with LIKE: incomplete
WHERE name NOT LIKE '%ml'
  AND name NOT LIKE '% g'
  AND name NOT LIKE '% kg'
  AND name NOT LIKE '% L'
  AND name NOT LIKE '% units'
  AND name NOT LIKE '% bags'

and over this data it would give the same 4 rows. But you'd be missing three things LIKE can't express:

  1. Demanding that a number sits in front of the unit. A product called "Hand soap ml" —a perfectly possible typo— would be classified as "has a format", because LIKE '%ml' only looks at the last two letters. The regular expression demands [0-9]+ in front and would catch it.
  2. Tolerating case variations. "Shower gel 500 mL" doesn't match '%ml' in PostgreSQL; with regex it's enough to swap ~ for ~*.
  3. Maintaining it. Six conditions chained with AND become eight the moment the catalogue adds cl and oz; the regular expression only adds two alternatives inside the parentheses.

That's the criterion for choosing: LIKE while the condition is a single fixed shape; regex as soon as alternatives, repetitions or character classes show up.

Conclusion

You now know how to search by pattern and, above all, when you shouldn't:

  • LIKE compares against a pattern, not against a value, and returns a boolean you can combine with AND, OR and NOT. NOT LIKE is its negation, with the same blindness to NULL as <>.
  • The two wildcards are % (zero or more characters) and _ (exactly one). A pattern with no wildcards is equivalent to =, and the pattern is always anchored to the whole string.
  • In PostgreSQL LIKE is case-sensitive. The three ways out are ILIKE (convenient but non-standard), LOWER(col) LIKE LOWER(pat) (portable) and a non-deterministic collation (a schema decision). In MySQL the default behaviour is the opposite, and in SQLite it only applies to ASCII.
  • To search for a literal % or _ you have to escape them: \% by default in PostgreSQL, or the character you declare with ESCAPE, which is the portable form.
  • When the condition involves alternatives, repetitions or character classes, LIKE falls short: in come SIMILAR TO (standard, little used) and above all PostgreSQL's regular expressions ~, ~*, !~, !~*, with which you've validated the 15 emails and spotted the 4 products with no size stated.
  • Performance depends on the pattern's first character: 'text%' can use a B-tree index; '%text%' forces the whole table to be read. For that case there's pg_trgm (module 8), and when what you need is a real search engine, full-text search.

In the next lesson, the IN and BETWEEN operators, you'll go on sharpening the filtering but along a different road: instead of text patterns, lists of values and ranges. You'll see how IN replaces an endless chain of ORs, how BETWEEN compacts a range always including both endpoints, and you'll run into one of the most expensive mistakes in all of SQL: NOT IN with a list containing a NULL doesn't return "the rest", it returns exactly zero rows.

◄ Previous
◄
Next ►
►

SQL Course

Module 1: Introduction to SQL

Module 2: Basic SQL queries

Module 3: Working with multiple tables

Module 4: Advanced data filtering

Module 5: Data manipulation

Module 6: Advanced SQL functions

Module 7: Subqueries and nested queries

Module 8: Indexes and performance tuning

Module 9: Transactions and concurrency

Module 10: Advanced topics

Module 11: SQL in practice

Module 12: Final project

The Project | About Us | Contribute | Donations | License
© Copyright 2026. All rights reserved