- 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.
Tip
You can use the search optimization service to improve the performance of queries that call this function. For details, see Search Optimization Service.
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
) insubject
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 '\\';
For examples of using escape characters, and in particular the backslash as an escape character, see the examples for the LIKE function.
Collation Details¶
Arguments with collation specifications are currently not supported.
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 | +-----------+