The Replace in String step performs a simple search and replace. This step supports regular expressions and group references. Group references are picked up in the Replace with column as $n where n is the number of the group.
Enter the following information in the transformation step name field.
- Step Name: Specify the unique name of the step on the canvas. The Step name is set to 'Replace in string' by default. You can customize the name or leave it as the default.
Field String Table
You can specify the replacement string and select options pertaining to it in the following table.
|In stream field||Specify the field to replace from the PDI input stream. Click Get Fields to add all fields from the input stream(s).|
|Out stream field||Specify the new field name to output to the PDI stream.|
|use RegEx||Select Y (Yes) or N (No) to indicate whether to use a regular expression to search for values.|
|Search||Specify whether to search for matched values of this string.|
|Replace with||Specify the string to replace the matched value.|
|Set empty string?||Select Y (Yes) or N (No) to indicate whether to replace null values with empty strings.|
|Replace with field||Specify a field value that will be used to replace the matched value.|
|Whole Word||Select Y (Yes) or N (No) to indicate whether to replace the entire word of the matched value.|
|Case sensitive||Select Y (Yes) or N (No) to indicate whether the search is case sensitive.|
|Is Unicode||Select Y (Yes) or N (No) to indicate whether the search value is a sequence of Unicode characters.|
Use Get Fields to add all fields from the PDI input stream.
Example: Using Regular Expression Group References
When using regex (regular expression) group references, matching patterns are represented by a sequence of integer values with a dollar sign prefix. For example: $1 for the first group match, $2 for the second group match, and so forth.
To replace a portion of the incoming value contained in the In stream field column, use one of the group value tokens to represent the portion of the string that group matched.
As an example, suppose the string value of In stream field is: "Homer Simpson"
To switch the first and last name, you would set up the table fields as follows:
- Set the use RegEx column to: Y
- The value of the Search column would look similar to: /([a-zA-Z]*) ([a-zA-Z]*)/
Each pattern inside the parenthesis represents a grouping, so that now $1 = “Homer” and $2 = “Simpson”
- To switch the first and last name, set the value of the Replace with field as: “$2 $1”
This places the string Simpson first, followed by Homer second.
For more information about regular expressions, see https://www.regular-expressions.info/brackets.html
Metadata Injection Support
All fields of this step support metadata injection. You can use this step with ETL Metadata Injection to pass metadata to your transformation at runtime.