Comment on page
REGEXP_REPLACE
Replaces every instance of the substring matched by a regular expression pattern in the given string with a specified replacement.
REGEXP_REPLACE(STRING, PATTERN, REPLACEMENT)
Type:
string
The string that is searched for a
PATTERN
match.Type:
string
A regular expression pattern.
This pattern must be a Java regular expression. String literals are unescaped. For example, to match
'\abc'
, a regular expression would be '^\\abc$'
.Type:
string
The string used to replace instances of
PATTERN
found in STRING
.Capturing groups can be referenced in
REPLACEMENT
using $g
for a numbered group or ${name}
for a named group. A dollar sign ($
) may be included in the replacement by escaping it with a backslash (\$
):Type:
string
STRING
with every instance of the substring matched by the regular expression PATTERN
replaced with REPLACEMENT
.STRING | PATTERN | REPLACEMENT | Output |
---|---|---|---|
Hello world | w | W | Hello World |
Hello World | o | 0 | Hell0 World |
Bond, James | (\w+)\W+(\w+) | The names $1. $2 $1. | The names Bond. James Bond. |
Hello World! | ! | '' | Hello World |
Hello xxWorld | x | '' | Hello World |
Hellol World | l(?= ) | '' | Hello World |
Hellol World | l(?![lod]) | '' | Hello World |
Helloo World | (?<=o)o | '' | Hello World |
Hello WorldW | (?<! )W | '' | Hello World |
null | '(?<! )W | '' | null |
CREATE JOB function_operator_example
ADD_MISSING_COLUMNS = true
AS INSERT INTO default_glue_catalog.upsolver_samples.orders_transformed_data MAP_COLUMNS_BY_NAME
SELECT
customer_email,
REGEXP_REPLACE(customer_email, '^.*@', '') AS email_domains
FROM default_glue_catalog.upsolver_samples.orders_raw_data
WHERE time_filter()
LIMIT 3;
customer_email | email_domains |
---|---|
att.net | |
hotmail.co.uk | |
aol.com |
Last modified 3mo ago