regexp_replace function

Applies to: Databricks SQL Databricks Runtime

Replaces all substrings of str that match regexp with rep.

Syntax

regexp_replace(str, regexp, rep [, position] )

Arguments

• str: A STRING expression to be matched.
• regexp: A STRING expression with a matching pattern.
• rep: A STRING expression which is the replacement string.
• position: A optional integral numeric literal greater than 0, stating where to start matching. The default is 1.

Returns

A STRING.

The regexp string must be a Java regular expression.

When using literals, use raw-literal (r prefix) to avoid escape character pre-processing.

Searching starts at position. The default is 1, which marks the beginning of str. If position exceeds the character length of str, the result is str.

Common error conditions

• INVALID_PARAMETER_VALUE.PATTERN
• INVALID_REGEXP_REPLACE

Examples

SQL

> SELECT regexp_replace('100-200', '(\\d+)', 'num');
 num-num

> SELECT regexp_replace('abc', '[invalid', 'x');
  Error: INVALID_PARAMETER_VALUE.PATTERN

Related functions

• like operator
• regexp_extract function
• regexp_extract_all function