LIKE multiple

All functions > COMPARISON > LIKE multiple

Case-sensitive or case-insensitive pattern matching: LIKE / ILIKE with ANY, ALL, or NONE and a parenthesized pattern list, plus binary ILIKE / NOT ILIKE on scalars.

Syntax

expr1 (LIKE|ILIKE) (ANY|ALL|NONE) (pat1, pat2, …)  ·  expr1 ILIKE pattern

Notes

Scalar ILIKE / NOT ILIKE: same % and _ wildcards as LIKE; case folding follows the backing engine / dialect (e.g. Trino compares lowercased VARCHARs).
Quantifiers ANY / ALL / NONE: keyword syntax is LIKE ANY (pat1, pat2, …) or ILIKE ALL (…); patterns are still typed as ARRAY(VARCHAR). Callable forms use LIKE_* / ILIKE_* with an ARRAY argument.
Predicate spellings such as NOT … LIKE ANY … are registered as internal names (NOT_LIKE_ANY, …) for translation only — they do not get standalone documentation pages.
Scalar case-sensitive LIKE without ANY/ALL/NONE stays on the dedicated LIKE / NOT LIKE operator pages.

Related Functions

Examples

LIKE

FeatureQL

SELECT
    f1 := 'hello' LIKE 'h%', -- Starts with 'h'
    f2 := 'hello' LIKE 'HELLO' -- Case-sensitive
;

Result

f1 BOOLEAN	f2 BOOLEAN
true	false

LIKE ANY

FeatureQL

SELECT
    f1 := 'hello' LIKE ANY ('%el%', '%lo'), -- Matches one of the patterns (LIKE is case-sensitive)
    f2 := 'hello' LIKE ANY ('z%') -- No pattern matches
;

Result

f1 BOOLEAN	f2 BOOLEAN
true	false

LIKE ALL

FeatureQL

SELECT
    f1 := 'hello' LIKE ALL ('%e%', '%o'), -- Both patterns match
    f2 := 'hello' LIKE ALL ('%e%', '%z') -- One pattern fails
;

Result

f1 BOOLEAN	f2 BOOLEAN
true	false

LIKE NONE

FeatureQL

SELECT
    f1 := 'hello' LIKE NONE ('%z%', '%q%'), -- No pattern matches
    f2 := 'hello' LIKE NONE ('%ell%') -- A pattern matches
;

Result

f1 BOOLEAN	f2 BOOLEAN
true	false

ILIKE

FeatureQL

SELECT
    f1 := 'Hello' ILIKE 'h%', -- Prefix, case-insensitive
    f2 := 'Hello' ILIKE '%O' -- Suffix
;

Result

f1 BOOLEAN	f2 BOOLEAN
true	true

ILIKE ANY

FeatureQL

SELECT
    f1 := 'Hello' ILIKE ANY ('%el%', '%lo') -- Case-insensitive any match
;

Result

f1 BOOLEAN
true

ILIKE ALL

FeatureQL

SELECT
    f1 := 'Hello' ILIKE ALL ('%e%', '%o') -- Both patterns match ignoring case
;

Result

f1 BOOLEAN
true

ILIKE NONE

FeatureQL

SELECT
    f1 := 'Hello' ILIKE NONE ('%z%') -- No case-insensitive match
;

Result

f1 BOOLEAN
true

NOT LIKE

FeatureQL

SELECT
    f1 := 'hello' NOT LIKE 'world', -- No match
    f2 := 'hello' NOT LIKE 'h%' -- Does match prefix
;

Result

f1 BOOLEAN	f2 BOOLEAN
true	false

NOT ILIKE

FeatureQL

SELECT
    f1 := 'Hello' NOT ILIKE 'z%' -- No match
;

Result

f1 BOOLEAN
true

Suggest changes to this page

Last update at: 2026/05/26 17:22:09

On this page