The String Manipulation (Multi Column) node is an extension of the String Manipulation node, applying the provided expression to every selected column in turn. This can be used to search and replace, compare, or concatenate strings in multiple columns at once.
In each iteration, one column name from the selection will be used as replacement for the $$CURRENTCOLUMN$$ placeholder in the expression. For instance, when selecting columns first_name and last_name, the expression capitalize($$CURRENTCOLUMN$$) would first evaluate capitalize($first_name$) and then capitalize($last_name$).
Simple Examples
Search and Replace
replace($$CURRENTCOLUMN$$, "Old", "New") searches for "Old" and replaces with "New" in all selected columns.
Concatenate Strings
join($$CURRENTCOLUMN$$, $Unit$) appends the string in the Unit column to each string in the selected columns. When selecting the Freeze and Boil columns and appending the results as new columns, the following table could result:
| Freeze | Boil | Unit | Freeze with Unit | Boil with Unit |
|---|---|---|---|---|
| 0 | 100 | ˚C | 0˚C | 100˚C |
| 32 | 212 | F | 32F | 212F |
| 273.15 | 373.15 | K | 273.15K | 373.15K |
Advanced Examples
Use Columns or Flow Variables to Control String Manipulation
You can use the value of an integer column to control integer parameters of a string manipulator: substr($$CURRENTCOLUMN$$, $Start Position$) extracts the substring of $$CURRENTCOLUMN$$ that starts at offset k, where k is the integer value in the column Start Position in the current row. For example, when the selected column is Customer and the results are appended as Customer Name Only, the expression substr($$CURRENTCOLUMN$$, $Start Position$) could be used to generate the following ouput:
| Customer | Start Position | Customer Name Only |
|---|---|---|
| Mr. Jones | 3 | Jones |
| Prof. Desmond | 5 | Desmond |
Flow variables can also be used to control the parameter of a string manipulation function. For instance, if there is an integer flow variable start-position available, the above example could be rewritten as substr($$CURRENTCOLUMN$$, $${Istart-position}$$).
Convert Strings to Missing Values
toNull(replace($$CURRENTCOLUMN$$, "N.A.", "")) searches for "N.A." in all selected columns, replaces them with "" and returns a missing value, if the resulting string is empty (toNull converts empty strings to a missing value). For multiple strings to replace, a regular expression separating the target strings with | can be used: regexReplace($$CURRENTCOLUMN$$, "N.A.|-", ""). This replaces both occurrences of "N.A." and "-" with empty strings.