rlike operator

Applies to: check marked yes Databricks SQL check marked yes Databricks Runtime 10.0

Returns true if str matches regex.

Syntax

str [NOT] rlike regex

Arguments

  • str: A STRING expression in UTF8_BINARY collation to be matched.
  • regex: A STRING expression with a matching pattern.

Returns

A BOOLEAN.

The regex string must be a regular expression. See Regular expressions for the supported syntax. When using literals, use raw-literal (r prefix) to avoid escape character pre-processing.

rlike is a synonym for regexp operator.

str NOT rlike ... is equivalent to NOT(str rlike ...).

Common error conditions

Examples

Test whether a string matches a pattern

> SELECT 'Databricks SQL' rlike r'^Data';
 true

> SELECT 'Spark SQL' rlike r'^Data';
 false

Validate a simple email format

> SELECT 'alice@example.com' rlike r'^\w+@\w+\.\w+$';
 true

> SELECT 'not-an-email' rlike r'^\w+@\w+\.\w+$';
 false

Match case-insensitively

> SELECT 'HELLO WORLD' rlike r'(?i)hello';
 true

Negate a match with NOT

> SELECT 'no digits here' NOT rlike r'\d';
 true

Match a literal backslash

A backslash is a regex metacharacter, so match a literal backslash with \\. A raw literal (r prefix) avoids having to also double the SQL escape character.

> SELECT r'%SystemDrive%\Users\John' rlike r'%SystemDrive%\\Users.*';
 true

> SELECT r'%SystemDrive%\Users\John' rlike '%SystemDrive%\\\\Users.*';
 true
  • Last updated on