Skip to main content
Last updated on

regexp operator

Applies to: check marked yes Databricks SQL check marked yes Databricks Runtime 10.4 LTS and above

Returns true if str matches regex. This function is a synonym for rlike operator.

Syntax​

str [NOT] regexp 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.

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

Common error conditions​

Examples​

Test whether a string matches a pattern​

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

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

Check whether a string contains a digit​

SQL
> SELECT 'order12345' regexp r'\d';
 true

Match case-insensitively​

Use the (?i) inline flag to ignore case.

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

Negate a match with NOT​

SQL
> SELECT 'no digits here' NOT regexp 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.

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

> SELECT r'%SystemDrive%\Users\John' regexp '%SystemDrive%\\\\Users.*';
 true
On this page