Replaces all substrings in a string that match a regular expression and returns the result as a new string.
REGEXP_REPLACE(str, pattern, replacement) returns a new VARCHAR from str with every substring matching the regular expression in pattern replaced by replacement.
Limitations
Supported only in Realtime Compute for Apache Flink that uses Ververica Runtime (VVR) 3.0.0 or later.
Syntax
VARCHAR REGEXP_REPLACE(VARCHAR str, VARCHAR pattern, VARCHAR replacement)Input parameters
| Parameter | Data type | Description |
|---|---|---|
str |
VARCHAR | The source string to search. |
pattern |
VARCHAR | The regular expression pattern to match. |
replacement |
VARCHAR | The string to substitute for each match. |
Returns NULL if any input parameter is NULL or if the regular expression is invalid.
Usage notes
Both pattern and replacement follow Java Pattern syntax.
Examples
The following examples use table T1 as the data source:
T1
| str1 (VARCHAR) | pattern1 (VARCHAR) | replace1 (VARCHAR) |
|---|---|---|
2014-03-13 |
- |
(empty string) |
| (empty string) | - |
(empty string) |
2014-03-13 |
(empty string) | s |
2014-03-13 |
( |
s |
100-200 |
(\\d+) |
num |
Query
SELECT REGEXP_REPLACE(str1, pattern1, replace1) AS `result`
FROM T1;Results
| result (VARCHAR) | Explanation |
|---|---|
20140313 |
Replaces all hyphens with an empty string. |
| (empty string) | Input string is empty; nothing to replace. |
s2s0s1s4s-s0s3s-s1s3s |
An empty pattern matches every position between characters, inserting s at each position. |
| NULL | ( is an invalid regular expression; returns NULL. |
num-num |
(\\d+) matches each digit group; both 100 and 200 are replaced with num. |