Transform Data Using Replace
Transform data in a column using a simple pattern match or a regular (regex) expression. For example, in a State column, you might change CA to California.
Example Replace Transforms Using Regular Expressions
Here are just a few examples of using regular (regex) expressions to transform data.
In these examples, the Search Expression shows what to enter in the String to replace field, and the Replace Value shows what to enter in the New string field.
Table 4-1 Example replace transforms
Search Expression | Replace Value | Original Text | Replaced Text | Notes |
---|---|---|---|---|
@([a-z]+)(?=\.[a-z]{3}) |
@example | MichelePFalk@yahoo.com | MichelePFalk@example.com | This example replaces domain details in email addresses. |
^Gray|Grey$ |
Silver |
Grey Gray Graystone |
Silver Silver Graystone |
The ^$ characters mean only look for entire string matches. The vertical bar is the regular expression for OR, so in this case the regular expression looks for either "Gray" or "Grey" and replace with "Silver".
The string Graystone isn't transformed because the regular expression is looking for entire value matches only. |
\d+ |
9999 |
8398 Park Street 123 Oracle Parkway |
9999 Park Street 9999 Oracle Parkway |
This regular expression looks for one" \d" or more "+" digits and replaces them with "9999". The replace also works when the original text has only three digits. |
([A-z]+)(\d+) |
$2 |
UA101654 US829383 |
101654 829383 |
This regular expression is looking for one "[A-z]" or more "+" consecutive letters followed by one" \d" or more "+" digits - each of the two expressions is bounded by parentheses, which captures two groups - the first one ([A-z]) and the second one (\d+) - these groups are numbered automatically and can then be used in the replacement by using the dollar sign for the second group, for example, "$2". |
([A-z]+)(\d+) |
Postal Code: $2 |
UA101654 US829383 |
Postal Code: 101654 Postal Code: 829383 |
This example uses the same match expression as the previous example, except that it shows how to insert your own replacement text in addition to a group. Text can be inserted before and after a recalled match group. |