Categories:

String & Binary Functions (Matching/Comparison)

[ NOT ] ILIKE

Performs a case-insensitive comparison to determine whether a string matches or does not match a specified pattern. For case-sensitive matching, use LIKE instead.

LIKE, ILIKE, and RLIKE all perform similar operations. However, RLIKE uses POSIX ERE (Extended Regular Expression) syntax instead of the SQL pattern syntax used by LIKE and ILIKE.

Tip

You can use the search optimization service to improve the performance of queries that call this function. For details, see Search Optimization Service.

See also:

[ NOT ] LIKE , [ NOT ] RLIKE

Syntax

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

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

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.

Returns

Returns a BOOLEAN or NULL.

  • When ILIKE is specified, the value is TRUE if there is a match. Otherwise, returns FALSE.

  • When NOT ILIKE is specified, the value is TRUE if there is no match. Otherwise, returns FALSE.

  • When either ILIKE or NOT ILIKE is specified, returns NULL if any argument is NULL.

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

  • There is no default escape character.

  • If you use the backslash as an escape character, then you must specify escape the backslash in the ESCAPE clause. For example, the following command specifies that the escape character is the backslash, and then uses that escape character to search for ‘%’ as a literal (without the escape character, the ‘%’ would be treated as a wildcard):

    'SOMETHING%' ILIKE '%\\%%' ESCAPE '\\';
    
    Copy

    For examples of using escape characters, and in particular the backslash as an escape character, see the examples for the LIKE function.

Collation details

Only the upper, lower, and trim collation specifications are supported. Combinations with upper, lower, and trim are also supported (for example, upper-trim and lower-trim), except for locale combinations (for example, en-upper).

Note

To use this function with a column that has the upper, lower, or trim collation specifiers, the 2024_02 behavior change bundle must be enabled in your account. Currently, it is enabled by default.

If the 2024_02 behavior change bundle is disabled in your account, this function will not behave as documented.

To enable this bundle in your account, execute the following statement:

SELECT SYSTEM$ENABLE_BEHAVIOR_CHANGE_BUNDLE('2024_02');
Copy

Examples

CREATE OR REPLACE TABLE ilike_ex(name VARCHAR(20));
INSERT INTO ilike_ex VALUES
      ('John  Dddoe'),
      ('Joe   Doe'),
      ('John_down'),
      ('Joe down'),
      (null);
Copy
SELECT * 
    FROM ilike_ex 
    WHERE name ILIKE '%j%h%do%'
    ORDER BY 1;
Copy
+-------------+                                                                 
| NAME        |
|-------------|
| John  Dddoe |
| John_down   |
+-------------+
SELECT *
    FROM ilike_ex
    WHERE name NOT ILIKE '%j%h%do%'
    ORDER BY 1;
Copy
+-----------+
| NAME      |
|-----------|
| Joe   Doe |
| Joe down  |
+-----------+
SELECT * 
    FROM ilike_ex 
    WHERE name ILIKE '%j%h%^_do%' ESCAPE '^'
    ORDER BY 1;
Copy
+-----------+                                                                   
| NAME      |
|-----------|
| John_down |
+-----------+