Categories:

String & Binary Functions (Matching/Comparison)

ILIKE ANY¶

Performs a case-insensitive comparison to match a string against any of one or more specified patterns. Use this function in a WHERE clause to filter for matches. For case-sensitive matching, use LIKE ANY instead.

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 ] ILIKE , LIKE ANY

Syntax¶

<subject> ILIKE ANY (<pattern1> [, <pattern2> ... ] ) [ ESCAPE <escape_char> ]
Copy

Arguments¶

Required:

subject

The string to compare to the pattern(s).

pattern#

The pattern(s) that the string is to be compared to. You must specify at least one pattern.

Optional:

escape_char

Character(s) inserted in front of a wildcard character to indicate that the wildcard should be interpreted as a regular character rather than as a wildcard.

Returns¶

Returns a BOOLEAN or NULL. The value is TRUE if there is a match. Otherwise, returns FALSE. 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.

  • The pattern is considered a match if the pattern matches the entire input string (subject). To match a sequence anywhere within a string, start and end the pattern with %, for example ‘%something%’.

  • NULL does not match NULL. In other words, if the subject is NULL and one of the patterns is NULL, that is not considered a match.

  • You can use the NOT logical operator before the subject to perform a case-insensitive comparison that returns TRUE if it does not match any of the specified patterns.

  • If the function is used with a subquery, the subquery should return a single row.

    For example, the following should be used only if the subquery returns a single row:

    SELECT ...
        WHERE x ILIKE ANY (SELECT ...)
    
    Copy

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, you must enable the 2024_02 behavior change bundle in your account.

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

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

Examples¶

This shows how to use ILIKE ANY:

Create a table that contains some strings:

CREATE OR REPLACE TABLE ilike_example(name VARCHAR(20));
INSERT INTO ilike_example VALUES
    ('jane doe'),
    ('Jane Doe'),
    ('JANE DOE'),
    ('John Doe'),
    ('John Smith');
Copy
SELECT * 
  FROM ilike_example 
  WHERE name ILIKE ANY ('jane%', '%SMITH')
  ORDER BY name;
Copy
+------------+                                                                  
| NAME       |
|------------|
| JANE DOE   |
| Jane Doe   |
| John Smith |
| jane doe   |
+------------+

For examples of how to use wildcard characters, see the documentation of the related function LIKE ANY.