regexp_like function

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

regexp_like( str, regex )

Arguments

  • str: A STRING expression 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.

Common error conditions

Examples

Test whether a string matches a pattern

> SELECT regexp_like('Databricks SQL', r'^Data');
 true

> SELECT regexp_like('Spark SQL', r'^Data');
 false

Validate a simple email format

> SELECT regexp_like('alice@example.com', r'^\w+@\w+\.\w+$');
 true

> SELECT regexp_like('not-an-email', r'^\w+@\w+\.\w+$');
 false

Match case-insensitively

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

> SELECT regexp_like('ERROR: disk full', r'(?i)error');
 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 regexp_like(r'%SystemDrive%\Users\John', r'%SystemDrive%\\Users.*');
 true

> SELECT regexp_like('%SystemDrive%\\Users\\John', '%SystemDrive%\\\\Users.*');
 true

Invalid regex pattern

> SELECT regexp_like('abc', '[invalid');
  Error: INVALID_PARAMETER_VALUE.PATTERN
  • Last updated on