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
    -- Starts with 'h'
    f1 := 'hello' LIKE 'h%',
    -- Case-sensitive
    f2 := 'hello' LIKE 'HELLO'
;

Result

f1 BOOLEAN	f2 BOOLEAN
true	false

LIKE ANY

FeatureQL

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

Result

f1 BOOLEAN	f2 BOOLEAN
true	false

LIKE ALL

FeatureQL

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

Result

f1 BOOLEAN	f2 BOOLEAN
true	false

LIKE NONE

FeatureQL

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

Result

f1 BOOLEAN	f2 BOOLEAN
true	false

ILIKE

FeatureQL

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

Result

f1 BOOLEAN	f2 BOOLEAN
true	true

ILIKE ANY

FeatureQL

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

Result

f1 BOOLEAN
true

ILIKE ALL

FeatureQL

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

Result

f1 BOOLEAN
true

ILIKE NONE

FeatureQL

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

Result

f1 BOOLEAN
true

NOT LIKE

FeatureQL

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

Result

f1 BOOLEAN	f2 BOOLEAN
true	false

NOT ILIKE

FeatureQL

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

Result

f1 BOOLEAN
true

Suggest changes to this page

Last update at: 2026/06/20 10:08:10

On this page