Replace Transform

NOTE: Transforms are a part of the underlying language, which is not directly accessible to users. This content is maintained for reference purposes only. For more information on the user-accessible equivalent to transforms, see Transformation Reference.

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.

Basic Usage

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.

Syntax and Parameters

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.

For more information on syntax standards, see Language Documentation Syntax Notes.

col

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

To specify multiple columns:

  • Discrete column names are comma-separated.
  • Values for column names are case-sensitive.

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

Output: Replaces value MyString in MyCol column with myNewString.

Usage Notes:

Required?Data Type
YesString (column name)

with

Merge Columns transformation: Specifies the delimiter between columns that are merged. If this parameter is not specified, no delimiter is applied.

Replace Text or Pattern transformation: Specifies the replacement value.

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.

Usage Notes:

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.

A pattern identifier that precedes the value or pattern to match. Define the after parameter value using string literals, regular expressions, or Cloud Dataprep patterns.

Usage Notes:

Required?Data Type
NoString (string literal or pattern)
  • The after and from parameters are very similar. from includes the matching value as part of the replaced string.
  • after can be used with either to, on, or before. See Pattern Clause Position Matching.

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.

Identifies the start and end point of the pattern to interest.

Parameter inputs are in the form of x,y where x and y are positive integers indicating the starting character and ending character, respectively, of the pattern of interest.

  • x must be less than y.
  • If y is greater than the length of the value, the pattern is defined to the end of the value, and a match is made.

Usage Notes:

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

before

A pattern identifier that occurs after the value or pattern to match. Define the pattern using string literals, regular expressions, or Cloud Dataprep patterns.

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

Output:

  • Replaces first three groups of digits in the credit_card column with asterisks, effectively masking the number.

Usage Notes:

Required?Data Type
NoString or pattern
  • The before and to parameters are very similar. to includes the matching value as part of the replaced string.
  • before can be used with either from, on, or after. See Pattern Clause Position Matching.

from

Identifies the pattern that marks the beginning of the value to match. Pattern can be a string literal, Cloud Dataprep pattern, or regular expression. The from value is included in the match.

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

Output:

  • All content from the string <END> to the end of the string value in MyCol is removed.

Usage Notes:

Required?Data Type
NoString or pattern
  • The after and from parameters are very similar. from includes the matching value as part of the replaced string.
  • from can be used with either to or before. See Pattern Clause Position Matching.

to

Identifies the pattern that marks the ending of the value to match. Pattern can be a string literal, Cloud Dataprep pattern, or regular expression. The to value is included in the match.

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

Output:

  • Replace first two number groups in the column ssn with asterisks to mask the data.

Usage Notes:

Required?Data Type
NoString or pattern
  • The before and to parameters are very similar. to includes the matching value as part of the replaced string.
  • to can be used with either from or after. See Pattern Clause Position Matching.

on

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

Identifies the pattern to match. Pattern can be a string literal, Cloud Dataprep pattern, or regular expression pattern.

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.

Usage Notes:

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

global

Indicates whether any match should be applied to one instance or to all.

  • (Default) If false, apply transform only to the first instance.
  • If true, apply to all found matches.

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.

Usage Notes:

Required?Data Type
No. Default is false.Boolean

Examples

Tip: For additional examples, see How-to Guides.

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

This example illustrates the different uses of the following transformations to replace or extract cell data:

  • set - defines the values to use in a predefined column. See Set Transform.

    Tip: Use the derive transform to generate a new column containing a defined set of values. See Derive Transform.

  • replace - replaces a string literal or pattern appearing in the values of a column with a specific string. See Replace Transform.
  • extract - extracts a pattern-based value from a column and stores it in a new column. See Extract Transform.

Source:

The following dataset contains contact information that has been gathered by your marketing platform from actions taken by visitors on your website. You must clean up this data and prepare it for use in an analytics platform.

LeadIdLastNameFirstNameTitlePhoneRequest
LE160301001JonesCharlesChief Technical Officer415-555-1212reg
LE160301002LyonsEdward 415-012-3456download whitepaper
LE160301003MartinMaryCEO510-555-5555delete account
LE160301004SmithTaliaEngineer510-123-4567free trial

Transformation:

Title column: For example, you first notice that some data is missing. Your analytics platform recognizes the string value, "#MISSING#" as an indicator of a missing value. So, you click the missing values bar in the Title column. Then, you select the Replace suggestion card. Note that the default replacement is a null value, so you click Edit and update it:

Transformation Name Edit column with formula
Parameter: Columns Title
Parameter: Formula if(ismissing([Title]),'#MISSING#',Title)

Request column: In the Request column, you notice that the reg entry should be cleaned up. Add the following transformation, which replaces that value:

Transformation Name Replace text or pattern
Parameter: Column Request
Parameter: Find `{start}reg{end}`
Parameter: Replace with Registration

The above transformation uses a Cloud Dataprep pattern as the expression of the on: parameter. This expression indicates to match from the start of the cell value, the string literal reg, and then the end of the cell value, which matches on complete cell values of reg only.

This transformation works great on the sample, but what happens if the value is Reg with a capital R? That value might not be replaced. To improve the transformation, you can modify the transformation with the following Cloud Dataprep pattern in the on parameter, which captures differences in capitalization:

Transformation Name Replace text or pattern
Parameter: Column Request
Parameter: Find `{start}{[R|r]}eg{end}`
Parameter: Replace with 'Registration'

Add the above transformation to your recipe. Then, it occurs to you that all of the values in the Request column should be capitalized in title or proper case:

Transformation Name Edit column with formula
Parameter: Columns Request
Parameter: Formula proper(Request)

Now, all values are capitalized as titles.

Phone column: You might have noticed some issues with the values in the Phone column. In the United States, the prefix 555 is only used for gathering information; these are invalid phone numbers.

In the data grid, you select the first instance of 555 in the column. However, it selects all instances of that pattern, including ones that you don't want to modify. In this case, continue your selection by selecting the similar instance of 555 in the other row. In the suggestion cards, you click the Replace Text or Pattern transformation.

Notice, however, that the default Replace Text or Pattern transformation has also highlighted the second 555 pattern in one instance, which could be a problem in other phone numbers not displayed in the sample. You must modify the selection pattern for this transformation. In the on: parameter below, the Cloud Dataprep pattern has been modified to match only the instances of 555 that appear in the second segment in the phone number format:

Transformation Name Replace text or pattern
Parameter: Column Phone
Parameter: Find `{start}%{3}-555-%*{end}`
Parameter: Replace with '#INVALID#'
Parameter: Match all occurrences true

Note the wildcard construct has been added (%*). While it might be possible to add a pattern that matches on the last four characters exactly (%{4}), that matching pattern would not capture the possibility of a phone number having an extension at the end of it. The above expression does.

NOTE: The above transformation creates values that are mismatched with the Phone Number data type. In this example, however, these mismatches are understood to be for the benefit of the system consuming your Cloud Dataprep output.

LeadId column: You might have noticed that the lead identifier column (LeadId) contains some embedded information: a date value and an identifier for the instance within the day. The following steps can be used to break out this information. The first one creates a separate working column with this information, which allows us to preserve the original, unmodified column:

Transformation Name New formula
Parameter: Formula type Single row formula
Parameter: Formula LeadId
Parameter: New column name 'LeadIdworking'

You can now work off of this column to create your new ones. First, you can use the following replace transformation to remove the leading two characters, which are not required for the new columns:

Transformation Name Replace text or pattern
Parameter: Column LeadIdworking
Parameter: Find 'LE'
Parameter: Replace with ''

Notice that the date information is now neatly contained in the first characters of the working column. Use the following to extract these values to a new column:

Transformation Name Extract text or pattern
Parameter: Column to extract from LeadIdworking
Parameter: Option Custom text or pattern
Parameter: Text to extract `{start}%{6}`

The new LeadIdworking2 column now contains only the date information. Cleaning up this column requires reformatting the data, retyping it as a Datetime type, and then applying the dateformat function to format it to your satisfaction. These steps are left as a separate exercise.

For now, let's just rename the column:

Transformation Name Rename columns
Parameter: Option Manual rename
Parameter: Column LeadIdworking1
Parameter: New column name 'LeadIdDate'

In the first working column, you can now remove the date information using the following:

Transformation Name Replace text or pattern
Parameter: Column LeadIdworking
Parameter: Find `{start}%{6}`
Parameter: Replace with ''

You can rename this column to indicate it is a daily identifier:

Transformation Name Rename columns
Parameter: Option Manual rename
Parameter: Column LeadIdworking
Parameter: New column name 'LeadIdDaily'

Results:

LeadIdLeadIdDailyLeadIdDateLastNameFirstNameTitlePhoneRequest
LE160301001001160301JonesCharlesChief Technical Officer#INVALID#Registration
LE160301002002160301LyonsEdward#MISSING#415-012-3456Download Whitepaper
LE160301003003160301MartinMaryCEO#INVALID#Delete Account
LE160301004004160301SmithTaliaEngineer510-123-4567Free Trial

Example - Using capture group references for replacements

The replace transform can take advantage of capture groups defined in the Cloud Dataprep patterns 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 Cloud Dataprep pattern:

Transformation Name Replace text or pattern
Parameter: Column camel_case
Parameter: Find `({lower})({upper})`
Parameter: Replace with '$1 $2'
Parameter: Match all occurrences true

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