replace

replace( target, pattern, replacement )

Replace string characters matching the given regular expression pattern with the supplied replacement value.

In the example below, the underscore character (_) is removed by replacing it with an empty character.

table new_table is select
    replace(column1, literal '_', literal '') as column2,
from original_table;

Removing Symbols and Words

Remove a comma, such as from a numeric string

// Source data: "23,345"
// Returns: "23345"
replace(source_column, literal ',', literal '')

Remove the dollar sign currency symbol ($).

Note that $ is a special pattern matching character, so we need to “escape it” by prepending it with a \ character.

// 
// Source data: "$23.98"
// Returns: "23.98"
replace(source_column, literal '\$', literal '')

Ensure only numeric characters are part of the output using \w “metacharacter” which matches “word” characters which includes characters a-z, A-Z, 0-9, including _ (underscore).

// Source data: "adunit 123456 display"
// Returns: "123456"
replace(source_column, literal '\w+', literal '')

Using Character Classes

Remove the dollar sign ($) and a comma (,) from a number using a character class, a list of characters enclosed in square brackets [].

Note that $ is a special pattern matching character, so we need to “escape it” by prepending it with a \ character.

// Source data: "$23,345.98"
// Returns: "23345.98"
replace(source_column, literal '[\$|,]', literal '')

Using Capture Groups

This example uses capture groups, which are defined by parenthesis () — in this case these are unnamed capture groups and are therefore referenced by their position in the list of capture groups.

// Source data: "adunit_1234567_video"
// Returns: "adunit 1234567 is a video ad"
replace(source_column, literal "(\w+)_(\w+)_(\w+)", literal "$1 $2 is a $3 ad")