like
operator
Applies to: Databricks SQL 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
orSOME
orALL
:Applies to: Databricks SQL Databricks Runtime
If
ALL
is specified thenlike
returnstrue
ifstr
matches all patterns, otherwise returnstrue
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