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

Query result

customer_email
remove_gmail

Gerald.Morgan@gmail.com

Gerald.Morgan

Teresa.Reed@gmail.com

Teresa.Reed

Larry.Torres@yahoo.com

Larry.Torres@yahoo.com

Last updated