Replace
Use the Replace tool to replace text values in your data.
Add multiple operations or conditions when you configure the Replace tool. However, you cannot use the same column with search type and find in different conditions if replace is different.
You can also use the drag-and-drop icon 
to order your operations the way you want.
Note
If you select RegEx as the search type, Varicent ELT only uses Python RegEx.
Input
The Replace tool requires one dataset with text columns
Configuration
Use the following configuration options to configure the Replace tool.
Go to the Pipes module from the side navigation bar.
From the Pipes tab, click an existing pipe to open, or create a new pipe. To create a new pipe, read the Creating a pipe documentation.
In the Pipe builder, add a data source to your pipe. For more information on adding a data source, see the Data Input tool.
Click
+ Tool.The Tools modal opens, where you can add tools, such as the Aggregate tool, to your pipe.
In the Tools modal, search for Replace , and then click + Add tool.
Note
You can also find the Replace tool in the Clean section.
Click the tool node and drag the line to the next tool to connect the tools. If you need to undo the action, click the line and then click Unlink.
In the configuration pane, in the Replace 1 section, under Target columns, select one or more text columns to replace.
Under Search type, select the type from the drop-down:
Exact match
Starts with
Ends with
Every
Regex
Under Find, type the text to use.
Optionally select Match word to match the word in your dataset.
Optionally select Ignore case to find the text regardless of case.
Under Replace, enter the text to use to replace the Find text.
Usage example
In this example, we have two values in the VoicemailPlan column that mean the same thing.
CustomerID | VoicemailPlan |
|---|---|
32131 | Y |
75674 | Yes |
44344 | Yes |
32424 | Y |
Let's clean this up so we have consistent values.
In the Target Columns field, select or type VoicemailPlan.
For Search Type, select Exact Match.
In the Find field, type Yes.
In the Replace field, type Y.
As a result, the data set now looks like this:
CustomerID | VoicemailPlan |
|---|---|
32131 | Y |
75674 | Y |
44344 | Y |
32424 | Y |