String & Binary Functions (Matching/Comparison)
Allows matching of strings based on comparison with a pattern. Unlike the LIKE function, string matching is case-insensitive.
LIKE, ILIKE, and RLIKE all perform similar operations; however, RLIKE uses POSIX EXE (Extended Regular Expression) syntax instead of the SQL pattern syntax used by LIKE and ILIKE.
<subject> ILIKE <pattern> [ ESCAPE <escape> ] ILIKE( <subject> , <pattern> [ , <escape> ] )
Subject to match.
Pattern to match.
Character(s) inserted in front of a wildcard character to indicate that the wildcard should be interpreted as a regular character and not as a wildcard.
SQL wildcards are supported in
An underscore (
_) matches any single character.
A percent sign (
%) matches any sequence of zero or more characters.
patterninclude newline characters (
ILIKE pattern matching covers the entire string. To match a sequence anywhere within a string, start and end the pattern with
Arguments with collation specifications are currently not supported.
CREATE OR REPLACE TABLE ilike_ex(subject varchar(20)); INSERT INTO ilike_ex VALUES ('John Dddoe'), ('Joe Doe'), ('John_down'), ('Joe down'), (null);SELECT * FROM ilike_ex WHERE subject ILIKE '%j%h%do%' ORDER BY 1; +-------------+ | SUBJECT | |-------------| | John Dddoe | | John_down | +-------------+SELECT * FROM ilike_ex WHERE subject ILIKE '%j%h%^_do%' ESCAPE '^' ORDER BY 1; +-----------+ | SUBJECT | |-----------| | John_down | +-----------+