ilike operator

Applies to: Databricks SQL Databricks Runtime

Returns true if str matches pattern with escape case-insensitively.

Syntax

str [ NOT ] ilike ( pattern [ ESCAPE escape ] )

str [ NOT ] ilike { ANY | SOME | ALL } ( [ pattern [, ...] ] )

Arguments

• str: A STRING expression in UTF8_BINARY or UTF8_LCASE collation.

• pattern: A STRING expression.

• escape: A single character STRING literal.

• ANY or SOME or ALL:

  If ALL is specified then [NOT] ilike returns true if str matches (or doesn't match) all patterns. Otherwise, or if ANY or SOME is specified, then [NOT] ilike returns true if str matches (or doesn't match) at least one pattern.

Returns

A BOOLEAN.

The pattern is a string which is matched literally and case-insensitively, with exception for the following special symbols:

• _ matches any one character in the input (similar to . in POSIX regular expressions)
• % matches zero or more characters in the input (similar to .* in POSIX regular expressions).

The default escape character is the '\'. If an escape character precedes a special symbol or another escape character, the following character is matched literally and in case-insensitive manner. It is invalid to escape any other character.

When using literals, use raw-literal (r prefix) to avoid escape character pre-processing.

str NOT ilike ... is equivalent to NOT(str ilike ...).

Examples

> SELECT ilike('Spark', '_PARK');
true

> SELECT r'%SystemDrive%\users\John' ilike r'\%SystemDrive\%\\Users%';
true

> SELECT r'%SystemDrive%\users\John' ilike '\%SystemDrive\%\\\\Users%';
true

> SELECT '%SystemDrive%/Users/John' ilike '/%SystemDrive/%//users%' ESCAPE '/';
true

> SELECT like('Spock', '_pArk');
false

> SELECT 'Spark' like SOME ('_ParK', '_Ock')
true

> SELECT 'Spark' like ALL ('_ParK', '_Ock')
false

Related functions

• like operator
• rlike operator
• regexp operator