notLike/NOTLIKE
The NOTLIKE is the inverse operation of LIKE, used to determine whether a specific character string does not match a specified pattern. It is equivalent to function notLike and predicate NOT LIKE. notLike supports both in-memory tables and DFS tables.
Syntax
match_expression notLike patternArguments
match_expression Any valid expression of character data type.
pattern is a string (case sensitive) to be searched in match_expression. The following wildcards can be used:
- % represents zero, one or multiple characters.
- ? represents one character.
If match_expression does not match pattern, it returns true, otherwise returns false.
The notLike keyword is usually used in a where clause to filter data based on a specific pattern. For example, to select data that does not start with "A", does not end with "B", or does not contain "Ca".
Examples
t= table(`a1`a2`a3`b`b2`b3`ca1`ca2 as id, 7 4 NULL 1 8 NULL 12 NULL as val)
select * from t where id notLike "a%"
// equivalent to select * from t where id not like "a%"| id | val |
|---|---|
| b | 1 |
| b2 | 8 |
| b3 | |
| ca1 | 12 |
| ca2 |
Query records where the id column does not end with "b".
select * from t where id notLike "%b"| id | val |
|---|---|
| a1 | 7 |
| a2 | 4 |
| a3 | |
| b2 | 8 |
| b3 | |
| ca1 | 12 |
| ca2 |
Query records where the id column does not contain “a“.
select * from t where id notLike "%a%"| id | val |
|---|---|
| b | 1 |
| b2 | 8 |
| b3 |