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

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

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

Check whether a string contains a digit

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

Match case-insensitively

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

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

Negate a match with NOT

> 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.

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

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