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.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 ...).
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
Related functions
Feedback
Coming soon: Throughout 2024 we will be phasing out GitHub Issues as the feedback mechanism for content and replacing it with a new feedback system. For more information see: https://aka.ms/ContentUserFeedback.
Submit and view feedback for