Replaces values within the specified column or columns based on the string literal, pattern, or location within the cell value, as specified in the transform. 

The replace transform is used primarily to match on patterns within a string. For entire cell replacement across all rows of the column, use the set transform. See Set Transform.

on parameter example: 

Specifies the string literal or pattern to match.

replace col: text on: 'honda' with:'toyota' global: true

Output: Replaces all instances in the text column of honda with toyota. If honda appears twice a cell, both instances are replaced with toyota.

at parameter example: 

Specifies the beginning character and ending character as index values for the match.

replace col: text at: 2,6 with:'replacement text'

Output: For all values in the text column, replace the substring between character 2 and 6 in the column with the value replacement text. If the length of the original cell value is less than 6, the replacement value is inserted.

replace col:column_ref with:'literal_replacement' [at:(start_index,end_index)] [on:string_literal_pattern] [global:true|false]

TokenRequired?Data TypeDescription
replaceYtransformName of the transform
colYstringName of column where to make replacements
withYsee belowLiteral value with which to replace matched values
afterNstringString literal or pattern that precedes the pattern to match
atNArrayTwo-integer array identifying the character indexes of start and end characters to match
beforeNstringString literal or pattern that appears after the pattern to match
fromNstringString literal or pattern that identifies the start of the pattern to match
toNstringString literal or pattern that identifies the end of the pattern to match
onNstringString literal or pattern that identifies the cell characters to replace
globalNbooleanIf true, all occurrences of matches are replaced. Default is false.

col

Identifies the column or columns to which to apply the transform. You can specify one or more columns. 

replace col: MyCol on: 'MyString' with: 'myNewString'

Output: Replaces value MyString in  MyCol column with myNewString.

Required?Data Type
YesString (column name)

with

For the replace transform, this value must be a literal value. You can apply values of String or other data types. After replacement, the column data type is re-inferred.

NOTE: Some regular expression capture groups with references (such as $2) are supported across all running environments. See Capture Group References.

Required?Data Type
YesLiteral of any data type

after

replace col:Primary_URL with:'' after:`http({any}|):`

Output: All content after the protocol identifier (http: or https:) is dropped.

Required?Data Type
NoString (string literal or pattern)

at

replace col: MyCol at: 2,6 with:'MyNewString'

Output: Replace contents of MyCol that starts at the second character in the column and extends to the sixth character with the value MyNewString.

Required?Data Type
Must use either on or at parameterArray of two Integers ( X,Y )

before

replace col:credit_card with:'****-***-***-' after:`{start}` before:`({digit}{4}){end}`

Output:

Required?Data Type
NoString or pattern

from

replace col: MyCol from: '<END>' with: ''

Output: 

Required?Data Type
NoString or pattern

to

replace col:ssn with:'***-**-' to:`{digit}{3}-{digit}{2}-`

Output:

Required?Data Type
NoString or pattern

on

replace col: MyCol on: `###ERROR` with:'No error here'

Tip: You can insert the Unicode equivalent character for this parameter value using a regular expression of the form /\uHHHH/. For example, /\u0013/ represents Unicode character 0013 (carriage return). For more information, see Supported Special Regular Expression Characters.

Required?Data Type
Must use either on or at parameterString or pattern

global

NOTE: If you have specified the pattern to match with the at parameter, then the number of possible replacement instances is only 1, so the global parameter is not used.

Required?Data Type
No. Default is false.Boolean


Example - Clean up marketing contact data with replace, set, and extract

Example - Using capture group references for replacements

The replace transform can take advantage of capture groups defined in the  and regular expressions used to search for values within a column. A capture group is a sub-pattern within your pattern that defines a value that you can reference in the replacement.

NOTE: For this transform, capture groups can be specified in the on parameter only.

In the following example, the on parameter defines two capture groups, and the with parameter references them in the replacement. In this example, any entry in the camel_case column that contains a lower-case letter followed immediately by an upper-case letter is replaced by the same value with a space inserted in the middle. The $1 value references the first capture group in the corresponding :

replace col:camel_case with:'$1 $2' on:`({lower})({upper})` global:true

Capture GroupDescriptionReplacement Reference
({lower})A single lower-case letter$1
({upper})A single upper-case letter$2