like operator

Applies to: check marked yes Databricks SQL check marked yes Databricks Runtime

Returns true if str matches pattern with escape.

Syntax 

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

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

Arguments

  • str: A STRING expression.

  • pattern: A STRING expression.

  • escape: A single character STRING literal.

  • ANY or SOME or ALL:

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

    If ALL is specified then like 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, with exception to 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. It is invalid to escape any other character.

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

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

Examples 

> SELECT like('Spark', '_park');
true

> SELECT r'%SystemDrive%\Users\John' like r'%SystemDrive%\\Users%';
true

-- When not using raw literals, the escape character must be escaped.
> SELECT r'%SystemDrive%\Users\John' like '%SystemDrive%\\\\Users%';
true

> SELECT '%SystemDrive%/Users/John' like '/%SystemDrive/%//Users%' ESCAPE '/';
true

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

> SELECT 'Spark' like SOME ('_park', '_ock')
true

> SELECT 'Spark' like ALL ('_park', '_ock')
false