- 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:
Syntax¶
<subject> ILIKE ANY (<pattern1> [, <pattern2> ... ] ) [ ESCAPE <escape_char> ]
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
) insubject
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 ...)
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');
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');SELECT * FROM ilike_example WHERE name ILIKE ANY ('jane%', '%SMITH') ORDER BY name;+------------+ | 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.