Skip to main content

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

    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 ...).

As defined by the SQL Standard, LIKE is always sensitive to trailing spaces, even if the collation is trailing space insensitive. For non-null str: str LIKE str || ' ' is always false.

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

The following collations are supported by LIKE:

  • UTF8_BINARY
  • UTF8_BINARY_RTRIM
  • UTF8_LCASE
  • UTF8_LCASE_RTRIM

For other collations use contains, startswith, or ends_with.

Examples​

SQL
> 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

-- like is always trailing blank sensitive
> SELECT 'Spark' COLLATE UTF8_BINARY_RTRIM like 'Spark ';
false
Last updated on