Like Operator

Learning Focus

Use this lesson to understand Like Operator with practical syntax and examples.

Concept Overview

Definition

LIKE matches a text value against a pattern. It is most commonly used in a WHERE clause for partial matches such as:

• starts with ('prefix%')
• ends with ('%suffix')
• contains ('%substring%')

PostgreSQL also provides:

• ILIKE (case-insensitive pattern matching)
• NOT LIKE / NOT ILIKE (negation)
• ESCAPE to treat % and _ as literal characters

Why is it important?

• Search UX: many simple search features begin with LIKE/ILIKE
• Data validation: detect unexpected patterns in emails, SKUs, filenames
• Operational workflows: quick filtering when exact matches are not practical

Where does it fit?

LIKE is part of filtering and conditions. You use it mostly with SELECT, and sometimes with UPDATE/DELETE to target rows that match a pattern.

---

Syntax & Rules

Core Syntax

SELECT select_list
FROM table_name
WHERE text_column LIKE pattern;

SELECT select_list
FROM table_name
WHERE text_column ILIKE pattern;

Available Options / Parameters

Element	Meaning	Example
%	matches zero or more characters	email LIKE '%@example.com'
_	matches exactly one character	code LIKE 'A_9'
ESCAPE	defines an escape character	LIKE '%\%%' ESCAPE '\\'
ILIKE	case-insensitive LIKE	email ILIKE '%@company.com'
NOT LIKE	negated match	name NOT LIKE 'Test%'

Key Rules and Considerations

• LIKE is case-sensitive in PostgreSQL; use ILIKE when case should be ignored.
• Prefix searches ('abc%') are usually much faster than contains searches ('%abc%').
• If the pattern comes from user input, escape % and _ or you will match more than intended.
• LIKE works on text; using it on numeric types requires explicit casting.
• For large-scale substring search, consider trigram indexes (pg_trgm) or full-text search.

---

Step-by-Step Examples

Example 1: Starts With (Prefix Match) (Beginner)

CREATE TABLE users (
  user_id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  email   text NOT NULL
);

INSERT INTO users (email) VALUES
  ('alice@company.com'),
  ('Alicia@company.com'),
  ('bob@Company.com'),
  ('carol@gmail.com');

SELECT user_id, email
FROM users
WHERE email LIKE 'ali%'
ORDER BY user_id;

Expected output:

 user_id | email
---------+-------------------
 1       | alice@company.com
(1 row)

Because LIKE is case-sensitive, Alicia@company.com does not match ali%.

---

Example 2: Ends With / Contains (Intermediate)

-- Ends with company domain (case-sensitive)
SELECT user_id, email
FROM users
WHERE email LIKE '%@company.com'
ORDER BY user_id;

Expected output:

 user_id | email
---------+--------------------
 1       | alice@company.com
 2       | Alicia@company.com
(2 rows)

This pattern starts with %, so it is typically not index-friendly on large tables.

---

Example 3: Case-Insensitive Match with ILIKE (Intermediate)

SELECT user_id, email
FROM users
WHERE email ILIKE '%@company.com'
ORDER BY user_id;

Expected output:

 user_id | email
---------+--------------------
 1       | alice@company.com
 2       | Alicia@company.com
 3       | bob@Company.com
(3 rows)

---

Example 4: Escaping % and _ (Advanced)

CREATE TABLE files (
  file_id  bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  filename text NOT NULL
);

INSERT INTO files (filename) VALUES
  ('report_2026_03.csv'),
  ('100%_done.txt'),
  ('100_percent_done.txt');

-- Find filenames containing a literal percent sign '%'
SELECT file_id, filename
FROM files
WHERE filename LIKE '%\%%' ESCAPE '\\'
ORDER BY file_id;

-- Find filenames that start with the literal string "report_"
SELECT file_id, filename
FROM files
WHERE filename LIKE 'report\_%' ESCAPE '\\'
ORDER BY file_id;

Expected output (literal '%'):

 file_id | filename
---------+-------------
 2       | 100%_done.txt
(1 row)

Expected output (literal '_'):

 file_id | filename
---------+-------------------
 1       | report_2026_03.csv
(1 row)

---

Practical Use Cases

1) Email domain filtering

SELECT user_id, email
FROM users
WHERE email ILIKE '%@gmail.com'
ORDER BY user_id;

2) Filter by known prefix (good performance path)

SELECT order_id, external_id
FROM orders
WHERE external_id LIKE 'ORD-%'
ORDER BY order_id DESC
LIMIT 50;

3) Filter by file extension

SELECT log_id, filename
FROM logs
WHERE filename LIKE '%.log'
ORDER BY log_id DESC;

4) Detect suspicious or reserved usernames

SELECT user_id, username
FROM users
WHERE username ILIKE '%admin%'
   OR username ILIKE '%support%';

5) Identify inconsistent formatting for cleanup

SELECT lead_id, phone
FROM leads
WHERE phone LIKE '% %'
   OR phone LIKE '%-%';

---

Common Mistakes & Troubleshooting

1) Assuming LIKE ignores case

Wrong SQL:

SELECT email
FROM users
WHERE email LIKE '%@company.com';

Bad outcome:

• bob@Company.com is missed.

Fix:

SELECT email
FROM users
WHERE email ILIKE '%@company.com';

---

2) Using LIKE on a non-text column

Wrong SQL:

SELECT *
FROM orders
WHERE order_id LIKE '12%';

Typical error:

ERROR:  operator does not exist: bigint ~~ unknown
HINT:  No operator matches the given name and argument types. You might need to add explicit type casts.

Fix:

SELECT *
FROM orders
WHERE order_id::text LIKE '12%';

---

3) Leading wildcard causes slow scans

Wrong approach:

SELECT *
FROM users
WHERE email ILIKE '%son%';

Bad outcome:

• On large tables this often forces a scan.

Fix options:

-- Option A: if the product allows it, change to a prefix pattern
WHERE email ILIKE 'son%'

-- Option B: for substring search at scale, use pg_trgm indexes (requires extension)
-- CREATE EXTENSION IF NOT EXISTS pg_trgm;
-- CREATE INDEX users_email_trgm_idx ON users USING gin (email gin_trgm_ops);

---

4) Forgetting to escape user input

Wrong approach:

• If a user searches for 100%, treating it as a pattern matches 100 plus anything.

Fix:

• Escape % and _ in user input and use ESCAPE consistently.

---

Debugging checklist:

1. Confirm whether you need case-sensitive (LIKE) or case-insensitive (ILIKE) matching.
2. Inspect the exact pattern string you are executing.
3. Check whether the pattern begins with % (performance red flag).
4. Use EXPLAIN to see whether an index is used.
5. Consider trigram indexes or full-text search for real substring search features.

---

Best Practices

• ✅ Prefer LIKE 'prefix%' when possible. ❌ Avoid LIKE '%contains%' on large tables without specialized indexing.
• ✅ Use ILIKE for user-facing search when case should be ignored. ❌ Avoid missing matches because of inconsistent casing.
• ✅ Escape % and _ when patterns come from user input. ❌ Avoid accidental wildcard expansion.
• ✅ Consider normalization (for example lower(email)) with a functional index for heavy search. ❌ Avoid repeatedly applying lower() at query time without indexing.
• ✅ Measure and validate with EXPLAIN. ❌ Avoid guessing about performance.

---

Hands-On Practice

Use this setup for the exercises:

CREATE TABLE practice_customers (
  customer_id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  full_name   text NOT NULL,
  email       text NOT NULL
);

INSERT INTO practice_customers (full_name, email) VALUES
  ('Alice Martin', 'alice@gmail.com'),
  ('Bob Marley', 'bob@yahoo.com'),
  ('Alicia Stone', 'stone.alicia@gmail.com'),
  ('George Smith', 'gsmith@hotmail.com');

Exercise 1 (Easy): Names that start with "Ali"

Task: Find customers whose name starts with Ali.

-- Your SQL here

Solution:

SELECT customer_id, full_name
FROM practice_customers
WHERE full_name LIKE 'Ali%'
ORDER BY customer_id;

---

Exercise 2 (Medium): Gmail addresses (case-insensitive)

Task: Find customers with Gmail emails, case-insensitively.

-- Your SQL here

Solution:

SELECT customer_id, email
FROM practice_customers
WHERE email ILIKE '%@gmail.com'
ORDER BY customer_id;

---

Exercise 3 (Advanced): Escape a literal underscore

Task: Create a table with values that include underscores and query for a literal underscore using ESCAPE.

-- Your SQL here

Solution:

CREATE TABLE practice_codes (
  code text NOT NULL
);

INSERT INTO practice_codes (code) VALUES
  ('A_1'),
  ('AB1'),
  ('A-1');

SELECT code
FROM practice_codes
WHERE code LIKE 'A\_%' ESCAPE '\\'
ORDER BY code;

---

Connection to Other Concepts

Concept	Why it matters
WHERE	LIKE and ILIKE are typically used in filters
Wildcards	% and _ control matching shape
AND / OR / NOT	combine pattern matches with other predicates
Indexes	determine whether searches are fast
Regular expressions (~, ~*)	more expressive matching than LIKE
Full-text search	better for search features at scale

---

Visual Learning Diagram

flowchart TD
  A[WHERE Filtering] --> B[LIKE]
  B --> C[% and _ Wildcards]
  B --> D[NOT LIKE]
  B --> E[ILIKE (Case-Insensitive)]
  B --> F[ESCAPE]
  B --> G[Performance]
  G --> H[Prefix Search]
  G --> I[Substring Search]
  I --> J[pg_trgm / Full-Text]

  classDef allNodes fill:#3e3e3e,stroke:#ffffff,stroke-width:2px,color:#f5f5f5
  classDef highlight fill:#3e3e3e,stroke:#ffffff,stroke-width:4px,color:#f5f5f5
  class A,B,C,D,E,F,G,H,I,J allNodes
  class B highlight

---

Common Pitfalls

Pitfall	Consequence	Prevention
Assuming LIKE ignores case	missed matches	use ILIKE or normalize text
Leading % on large tables	slow scans	prefer prefix patterns or trigram/full-text indexing
Not escaping user input	incorrect matches	escape %/_ and use ESCAPE
Using LIKE on numeric columns	errors or confusing casts	cast explicitly or use numeric comparisons
Treating LIKE as a search engine	poor relevance/performance	adopt full-text search for real search features

---

Quick Reference

col LIKE 'A%';
col LIKE '%A%';
col LIKE 'A_B';
col ILIKE '%@company.com';
col LIKE '%\%%' ESCAPE '\\';

What's Next

• Previous: AND, OR, NOT Operators - Review the previous lesson to reinforce context.
• Next: Wildcards ('%' and '_') - Continue to the next concept with incremental complexity.
• Module Overview - Return to this module index and choose another related lesson.