Links

REPLACE

Replaces or removes a substring within the given string.

Syntax

REPLACE(STRING, SEARCH [, REPLACE])

Arguments

STRING

Type: string
The string to search.
Type: string
The substring to search for in STRING.
If this is an empty string, then REPLACE is inserted in front of every character and at the end of the STRING.

REPLACE

Type: string
Default: ''
(Optional) The string used to replace all found instances of SEARCH.
If omitted, all instances of SEARCH are removed from STRING.

Returns

Type: string
A substring of the input STRING.

Examples

STRING
SEARCH
REPLACE
Output
string
g
strin
a
a
''
a
a
b
b
123
1
23
word
word
bird
bird
word
bird
thing
word
word
or
wd
word
null
null
null
bird
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,
REPLACE(customer_email, '@gmail.com', '') AS remove_gmail
FROM default_glue_catalog.upsolver_samples.orders_raw_data
WHERE time_filter()
LIMIT 3;

Query result

customer_email
remove_gmail
Gerald.Morgan
Teresa.Reed