REGEXP_REPLACE

Replaces every instance of the substring matched by a regular expression pattern in the given string with a specified replacement.

Syntax

REGEXP_REPLACE(STRING, PATTERN, REPLACEMENT)

Arguments

STRING

Type: string

The string that is searched for a PATTERN match.

PATTERN

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

See the RegEx pattern table for more information.

REPLACEMENT

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 (\$):

Returns

Type: string

STRING with every instance of the substring matched by the regular expression PATTERN replaced with REPLACEMENT.

Examples

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

Transformation job example

SQL

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 $commit_time BETWEEN run_start_time() AND run_end_time()
    LIMIT 3;

Query result

customer_email	email_domains
[e-mail]	att.net
[e-mail]	hotmail.co.uk
[e-mail]	aol.com