Categories:
String & Binary Functions (General)

ILIKE

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.

See also:
LIKE , RLIKE

Syntax

<subject> ILIKE <pattern> [ ESCAPE <escape> ]

ILIKE(<subject>, <pattern> [, <escape> ] )

Arguments

Required:

subject
Subject to match.
pattern
Pattern to match.

Optional:

escape
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.

Usage Notes

  • SQL wildcards are supported in pattern:
    • An underscore (_) matches any single character.
    • A percent sign (%) matches any sequence of zero or more characters.
  • Wildcards in pattern include newline characters (\n) in subject as matches.
  • ILIKE pattern matching covers the entire string. To match a sequence anywhere within a string, start and end the pattern with %.

Examples

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%';

+-------------+
| SUBJECT     |
|-------------|
| John  Dddoe |
| John_down   |
+-------------+

SELECT * FROM ilike_ex WHERE subject ILIKE '%j%h%^_do%' ESCAPE '^';

+-------------+
| SUBJECT     |
|-------------|
| John_down   |
+-------------+