Search Text with ILIKE
Part of the First Queries section of Coddy's PostgreSQL journey. Lesson 9 of 20.
PostgreSQL ILIKE matches text without distinguishing letter case according to the active locale. A percent sign matches any number of characters; an underscore matches exactly one character. Use single quotes around the pattern.
Input table products:
id | name | category | price | stock | active | note |
|---|---|---|---|---|---|---|
1 | Map | travel | 12 | 5 | True | NULL |
2 | Notebook | books | 8 | 0 | True | Recycled |
3 | Atlas | books | 24 | 3 | False | NULL |
4 | Mug | home | 12 | 8 | True | Blue |
5 | lamp | home | 30 | 2 | False | Clearance |
6 | Mini map | travel | 6 | 10 | True | NULL |
SELECT id, name FROM products WHERE name ILIKE 'm%' ORDER BY id;Map, Mug and Mini map begin with m regardless of its case. A pattern without percent signs matches the entire text rather than searching inside it. These exercises use ASCII names so locale differences do not affect the answer.
Use ILIKE for case-insensitive pattern matching. Surround a search term with % to find it anywhere.
Challenge
EasyReturn id and name for products whose name contains map in any letter case. Order by id.
The input tables are shown in the test panel. Each run starts from fresh data. Return the named columns in the stated order; do not hard-code the displayed rows.
Try it yourself
-- Write your SQL here.
This lesson includes a short quiz. Start the lesson to answer it and track your progress.
All lessons in First Queries
2Filter the Catalog
Filter with WHERECombine ConditionsMissing Values with NULLSearch Text with ILIKEFind Dispatch CandidatesPractice on your own: PostgreSQL playground