Upsolver SQLake
Contact Support
Search
⌃K
Links
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.

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

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 time_filter()
LIMIT 3;

Query result

customer_email
email_domains
att.net
hotmail.co.uk
aol.com
Previous
REGEX_NAMED_GROUPS
Next
Date and time functions
Last modified 3mo ago