PostgreSQL 18 reference · String Functions

PostgreSQL ILIKE and LIKE Operators

Match a string against a LIKE pattern ignoring case: % matches any text and _ one character. ~~* is the operator form.

PostgreSQL 18

Create a free account to save this page, take notes and track your progress. Sign in

Syntax

string ILIKE pattern  |  string LIKE pattern

Parameter	Description
`string`	The input text.
`pattern`	`%` any characters, `_` one character; `ESCAPE` sets an escape character.

boolean.

Case-insensitive search

SELECT name FROM products WHERE name ILIKE '%desk%';

Output

       name
-------------------
 Desk Lamp
 Standing Desk Mat
(2 rows)

Run this on your computer — see “Get Started” for setup.

LIKE is case-sensitive

SELECT 'Desk' LIKE 'd%' AS with_like, 'Desk' ILIKE 'd%' AS with_ilike;

Output

 with_like | with_ilike
-----------+------------
 f         | t
(1 row)

Run this on your computer — see “Get Started” for setup.

REGEXP_MATCH() · Return the captured groups of the first regular-expression match as a text array, or NULL if there's no match.
STARTS_WITH() · Return true if a string starts with a given prefix. The ^@ operator does the same and can use an index.
LOWER() · Convert a string to lowercase using the database's locale, so letters such as É and Ü work too.
SQL LIKE and Wildcards · tutorial