The Find and Replace transformation allows you to quickly find specific data and replace it with another value, from within the Data Transformation module. This is an inline transformation that can be directly performed on data in the data preview panel. However, this can only be applied to Text fields.
Using this, you can manipulate string data directly by entering the exact text, or by using regular expressions, which are special text string (or syntax) used to define search patterns. To learn more about regular expressions (or regex), refer to this external guide.
Instructions
This transformation is done directly on column fields (in the data preview panel) once a step has been configured and data generated in the Data Transformation module. Follow the instructions below to carry this out:
Click on the menu icon next to a text field’s name in the data preview panel, and select Find and Replace.
Alternatively, you can click on the menu option next to a text field in the Configure panel, under the Fields tab, and select Find and Replace.
A new Find and Replace popup will appear. You can locate data that you wish to replace by either writing it exactly as is in the Find text field, or by providing regex syntax that defines a search pattern to locate data.
Finding exact text: If you entered the exact data value in the field, then continue with the steps below:
Ensure that the Regular Expression toggle is disabled.
Enter the exact data values that need to be replaced in the Find field.
Select the Match Case checkbox to search for text with the same letter case (upper, lower, capital, etc.) as specified in the Find field.
Select the Match Entire String checkbox to search for the entire text line in the data. (The value will not be replaced if some of the specified text matches; the entire string needs to be matched.)
Then enter the new value with which to replace your selected text.
Click on the add (plus) button to save this replacement rule.
Using Regular Expressions: Follow the below steps only if using regular expressions to search for pattern in data.
Enable the Regular Expression toggle.
Enter the regex syntax in the Find field.
Then enter the new value with which to replace data through pattern matching.
Click on the add (plus) button to save this rule. It will appear under the Rule section.
You can add more rules to replace more text data, by repeating the above steps. Note, however, that the rules will be executed in sequence, from top to bottom.
You can also delete a rule by hovering over it in the Rule list, and then clicking on the delete icon.
Once you are done creating the replacement rules, click Submit to apply them all to the data.