ETL: REPLACE

Replace all occurrences of a search substring with a new value in a string field. Use REPLACE for text cleanup and formatting in ETL pipelines.

Description

In a string field, replaces all occurrences of a string with a different string.

Syntax

REPLACE(string, regex``, new_string)

Arguments

string - string expression to search

regex - string expression indicating a regular expression to search for

new_string - replacement string expression

Examples

REPLACE('President John Smith',’President’,’Ex-President’) returns ‘ex-President John Smith’

Notes

regex is a regular expression to match the string. If you want to include special characters such as ’[’ in the string literal, it is necessary to escape them by prefixing them with double backslashes (e.g. ’\\[’). If you want to replace \ (backslash) use the following regex ’[\\\\]’, that will replace one \ (backslash).

Return value datatype

String

Impact of null value

If any (or all) inputs are null, returns null.