• For more information, see Overview of Pattern Matching.
• For more information on Pattern syntax, see Text Matching.

Replace Methods

You can use the Replace text or patterns  transformation to replace values in one or more columns with literal values, , or regular expressions through any of the following methods. You can use this transformation to replace missing, mismatched, or bad data using the following methods.

Replace by selection

When you select a piece of text in the data grid, the replace suggestion card displayed in the Selection Details panel on the right side may contain Pattern-based options for finding the selected value and similar values in the column of data. You can use these suggestions to replace column values.

Steps:

1. Select the data you want to replace. The suggestion cards are displayed.
2. In the Selection Details panel on the right side, select the Replace pattern suggestion card and click Edit.
3. The Replace text or patterns transformation is specified for you in the Transform Builder, where you can modify the Find value and other parameters as needed. See example below. 

Replace using Transformer toolbar 

In the Transformer toolbar at the top of the grid, click Replace > Text or Pattern . The Replace text or pattern transformation is displayed in the Transform Builder. For more information, see Transformer Toolbar.

For more information on procedures, see "Replace using Transform Builder" below.

Replace using Column Details panel 

You can review sets of patterns for the selected column in the Column Details panel. When you select a column in the Column Details panel, you are prompted with a set of suggested patterns. 

For more information on suggestions, see Overview of Predictive Transformation.

Replace using Transform Builder

When a pattern suggestion is selected, it is specified in the Transform Builder for review and addition to your recipe. In the Transform Builder, you can select one or more columns to replace text or patterns. 

Steps:

The following steps describe how to build a pattern-based replacement transformation from scratch in the Transform Builder.

1. Enter Replace text or pattern in the Search panel.
2. Select an individual column or multiple columns from the following options:
   • Multiple: Select one or more columns from the drop-down list. 
   • All: Select all columns in the dataset. See below for an example.
   • Range: Specify a start column and an ending column. All columns in between are selected. 

   • Advanced: Specify the columns using a comma-separated list. You can combine multiple and range options under Advanced. 

     • Ranges of columns can be specified using the tilde (~) character.

     • The following example range selects from the dataset as displayed in the data grid column1, column3, and the range of columns between column5  and column8, inclusive:

       column1,column3,column5~column8

3. In the Find text box, enter the text value or pattern that matches the value you want to replace. For more information, see "Find Values in a Column" below.
4. In the Replace text box, enter the value to replace the found text.
5. For additional controls, click Advanced Options:
   1. Start search after: Enter a text or pattern that precedes the value you want to replace. See below example.
   2. Start search before: Enter a text or pattern that follows the value you want to replace. See below example.
   3. Ignore case: If selected, case is ignored when matching.
   4. Match all occurrences: If selected, all occurrences of the found text in the column are matched and replaced.
6. Click Add. The transformation is added to your recipe, and the selected columns are replaced with appropriate patterns in the data grid.

Find Values in a Column

The Replace with text or pattern transformation enables you to replace values within the specified column or columns based on a string literal or . When you specify the transformation in the Transform Builder, the Find textbox can be populated with one of the following types of values:

Examples

The following examples demonstrate how  can be used to find and replace values within a column or set of columns.

Replace first three characters 

This example uses   to find the first three characters. In this example, the first three characters of the Customer ID column are replaced with the value CustID- for the selected column in the dataset.

Transformation:

Results:

Replace using literal expressions 

This example is based on the search and replace content in your dataset using literals. In the following example, the value ##CLT_NAME## is replaced with Our Customer, Inc. across all columns in the dataset.

Transformation:

Replace string of four digits

The following example uses   to find a string of four digits. The replacement is based on the structure of the data, not on the type of data. If you have data that are not credit card numbers yet follows the four-digit pattern, those values can also be replaced. In this example, the myCreditCardNumbers column is masked with XXXX.

Transformation:

Results:

Using capture groups

The previous example captures aspects of the found pattern for use during replacement. A capture group is a mechanism in  or regular expressions to capture one or more parts of the matched values into variables. 

In the example, the last four-digit segment of the  is surrounded by parentheses:

({digit}{4}){end}

This group of digits is captured as the first (and only) capture group. In the replacement string, it is referenced as: 

$1

You can have multiple capture groups in a single pattern. In the replacement, these capture groups can be referenced sequentially left-to-right from the pattern: $1, $2, and so on.

For more information, see Capture Group References. 

Replace date and time patterns

The following example is based on replacing the date and time using the pre-configured suggestions displayed in the search context panel. In this example, the date   yy/mm/dd is replaced with mm/dd/yy.

Transformation:

Results:

Replace based on position

You can specify replacements based on the character position of values in your source column values. This method of finding and replacing values is useful if the source column data is consistently structured.

For example, suppose you have dates in the following format:

Transformation:

Suppose you wanted to replace the value for the month with Month, you could add the following transformation step:

Results:

To replace the four digits of the year, you could perform a basic replace text or pattern transformation with a pattern to find of the following:

Replace alpha-numeric and position patterns

You can use alpha-numeric and position  for replacing the customer's address in the  the dataset. In this example, {alpha-numeric} pattern is applied to find the customer's addresses and used {start} and {end} pattern to mention the position of replacement. For more information on Pattern Syntax, see  Text Matching .

Transformation:

Results:

Replace using special patterns

You can use the following special  tokens to search for matches in your source values. In some cases, these  are consistent with the patterns used for specific data types.

`{at-username}`

`{hashtag}`

`{hex}`

`{phone}`

`{email}`

`{url}`