ilike operator

Applies to: check marked yes Databricks SQL check marked yes Databricks Runtime 9.1 and above

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.

String literals are unescaped. For example, in order to match '\abc', the pattern should be '\\abc'.

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

Examples 

> SELECT ilike('Spark', '_PARK');
true
> SELECT '%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