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

STRINGPATTERNREPLACEMENTOutput

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_emailemail_domains

Mary.Smith3@att.net

att.net

Jordan.Richardson7@hotmail.co.uk

hotmail.co.uk

Jason.Ramirez@aol.com

aol.com

Last updated