Categories:
String & Binary Functions (Matching/Comparison)

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.

Collation:Not supported
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%'
    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 |
+-----------+