- Categories:
String & Binary Functions (Matching/Comparison)
ILIKE ANY¶
Allows case-insensitive matching of strings based on comparison with one or more patterns.
The operation is similar to LIKE
. If the input string matches any of the patterns, this returns the input string.
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¶
The data type of the returned value is VARCHAR.
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.
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 ...)
Examples¶
This shows how to use ILIKE ANY:
Create a table that contains some strings:
CREATE OR REPLACE TABLE ilike_example(subject varchar(20)); INSERT INTO ilike_example VALUES ('jane doe'), ('Jane Doe'), ('JANE DOE'), ('John Doe'), ('John Smith');SELECT * FROM ilike_example WHERE subject ILIKE ANY ('jane%', '%SMITH') ORDER BY subject; +------------+ | SUBJECT | |------------| | 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.