ILIKE and case-insensitive search

ILIKE searches text without caring whether letters are uppercase or lowercase, so the same word in any capitalization counts as a match.

About 5–10 minutes · no sign-up needed

Example

Finding Retail Text Matches with ILIKE

SELECT * FROM products
WHERE brand ILIKE '%nike%'

Result:

product_id	brand	category	price
P2001	NikeRunPro	Fashion	89.99
P2002	NIKERUNPRO	Fashion	94.99
P2003	nikerunpro	Fashion	79.99

The asterisk symbol means all columns.
```
SELECT *
```
FROM tells SQL which table to pull the data from.
```
SELECT *
FROM products
```
WHERE keeps only the rows that match your condition.
```
SELECT *
FROM products
WHERE brand ILIKE '%nike%';
```

Try each one in your head first, then open the answer.

1. Using customers, write a case-insensitive query to find rows where phone starts with the letter '5' (uppercase or lowercase).

SELECT * FROM customers WHERE phone ILIKE '5%'

2. Using products, write a case-insensitive query to find rows where product_id starts with the letter 'p' (uppercase or lowercase).

SELECT * FROM products WHERE product_id ILIKE 'p%'

3. Using suppliers, write a case-insensitive query to find rows where country starts with the letter 'n' (uppercase or lowercase).

SELECT * FROM suppliers WHERE country ILIKE 'n%'

4. Using employees, write a case-insensitive query to find rows where role starts with the letter 'a' (uppercase or lowercase).

SELECT * FROM employees WHERE role ILIKE 'a%'

5. Using reviews, write a case-insensitive query to find rows where review_id starts with the letter 'r' (uppercase or lowercase).

SELECT * FROM reviews WHERE review_id ILIKE 'r%'

Learn it by doing. This lesson is a short hands-on level on Bampaa: see it work, then build and write it yourself, with instant feedback.

About 5–10 minutes · no sign-up needed