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: ASTRINGexpression. -
pattern: ASTRINGexpression. -
escape: A single characterSTRINGliteral. -
ANYorSOMEorALL:Applies to:
Databricks SQL Databricks RuntimeIf
ALLis specified thenlikereturnstrueifstrmatches all patterns, otherwise returnstrueif 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: Databricks SQL Databricks Runtime 17.2 and above
The following collations are supported by LIKE:
UTF8_BINARYUTF8_BINARY_RTRIMUTF8_LCASEUTF8_LCASE_RTRIM
For other collations use contains, startswith, or ends_with.
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
-- like is always trailing blank sensitive
> SELECT 'Spark' COLLATE UTF8_BINARY_RTRIM like 'Spark ';
false