ilike operator

Applies to: check marked yes Databricks SQL check marked yes 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.

  • pattern: A STRING expression.

  • escape: A single character STRING literal.

  • ANY or SOME or ALL:

    If ALL is specified then ilike returns true if str matches all patterns, otherwise returns true if it matches 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