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.
\n \nIn 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$) .
\n \nreplace($$CURRENTCOLUMN$$, "Old", "New") searches for "Old" and replaces with "New" in all selected columns.
\n \njoin($$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:
\n \n FreezeBoilUnitFreeze with UnitBoil with Unit0100˚C0˚C100˚C32212F32F212F273.15373.15K273.15K373.15K\n \nYou 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:
\n \n CustomerStart PositionCustomer Name OnlyMr. Jones3JonesProf. Desmond5Desmond\n \nFlow 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}$$).
\n \ntoNull(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.
\n \n \n ","shortDescription":"Apply string transformations to several columns at once. The results can either replace the values in the respective source columns or be appended into a new columns.","keywords":["Capitalize","Concatenate","Strip","Multi-field formula"],"deprecated":false,"streamable":true,"tags":["Manipulation","Column","Convert & Replace","Streamable"],"icon":"data:image/png;base64,iVBORw0KGgoAAAANSUhEUgAAABAAAAAQCAYAAAAf8/9hAAAABmJLR0QA/wD/AP+gvaeTAAAACXBIWXMAAA7EAAAOxAGVKw4bAAAAB3RJTUUH5AQBDDQ4lg+AnAAAAJlJREFUOMulk1EOwyAMQ58Rd6jUHrbc3PsZE8oCdBpSROIQsKMgwAzLtgAkPcJLT/YDsWiHyzb/rJqBI83IIObq7OaMetaL0oNusaC19sm11pzd+N5MtzHe+eWXhmWyCsB5XGT0Ae77ZivhPK6phJ20rYQZs+UcZBK6//VANomSPJuDeKbuqK8mEaCMQCxa/UzbkuS6atQT/AUxb7dnibRCfwAAAABJRU5ErkJggg==","views":[],"inPorts":[{"objectClass":"org.knime.core.node.BufferedDataTable","color":"000000","dataType":"Table","optional":false,"name":"Input data","description":"Input data with multiple columns to proces."}],"outPorts":[{"objectClass":"org.knime.core.node.BufferedDataTable","color":"000000","dataType":"Table","optional":false,"name":"Output data","description":"Input data with replaced or appended columns."}],"dynInPorts":[],"dynOutPorts":[],"additionalSiteInformation":{"url":"https://update.knime.com/analytics-platform/5.12","trusted":true,"enabledByDefault":true,"name":"KNIME Analytics Platform 5.12 Update Site","version":"5.12.0"},"dialog":[{"sectionName":"String Manipulation","fields":[{"name":"Column Filter","description":"The columns that the node should loop over. These columns can be dynamically referenced in the expression using $$CURRENTCOLUMN$$ . Dynamically referenced means that $$CURRENTCOLUMN$$ refers to different columns, depending on the columns that are selected in the column filter. It is allowed to include non-string columns from the column filter, but their values will be converted to string before substituting them for $$CURRENTCOLUMN$$ .","optional":false},{"name":"Column List","description":"Available columns in the input data table. They can be statically referenced in an expression, meaning that they always refer to the same column in the input table, no matter which columns are selected to loop over. For instance, the column age can be referenced as $age$ . Values will not automatically be converted to string. If necessary, convert to string, e.g., string($age$) .","optional":false},{"name":"Flow Variable List","description":"Available flow variables, which can be used as variables in the expression. Like statically referenced columns, their values are not automatically converted to string. This makes it easier to use them to control non-string parameters of string manipulation functions, like in the substring example above.","optional":false},{"name":"Category","description":"The string function categories.","optional":false},{"name":"Function","description":"List of string functions in the selected category.","optional":false},{"name":"Description","description":"Description and example for the selected function.","optional":false},{"name":"Expression","description":"The expression editor.","optional":false},{"name":"Append Selected Columns with Suffix","description":"If selected, the computed values columns will be appended as new columns, using the original column name with the specified suffix.","optional":false},{"name":"Replace Selected Columns","description":"If selected, the computed values will replace the values in the columns that were selected to loop over.","optional":false},{"name":"Insert missing values as null","description":"When any of the referenced columns (statically or dynamically, see above) contain missing values, the expression can either be evaluated with the missing values set to null or be skipped entirely, simply returning a missing value for the output.","optional":false},{"name":"Fail if expression can not be evaluated","description":"Whether to stop node execution when an expression can not be evaluated. This could be the case if an input column contains values that the expression can not handle (e.g., toInt() on a column that contains "1", "2", "zebra", "4", "5" would fail on the third row). The default behavior is to stop execution, but disabling this option will output missing values instead.","optional":false}]}],"links":[],"kudos":[{"accountId":"account:user:3784265b-0f76-45f2-9a61-e472e4206f70","user":"nimantha","date":"2021-02-07T17:46:27+00:00"},{"accountId":"account:user:751e2226-1074-4bf6-b7af-29a0c091d977","user":"gimbo","date":"2021-02-22T14:38:31+00:00"},{"accountId":"account:user:552210dd-3dfc-4b47-bbe6-477d972e6b9e","user":"nilotpalc","date":"2022-05-12T08:11:33+00:00"}],"kudosCount":3,"bundleInformation":{"bundleVersion":"5.12.0.v202606171353","bundleName":"KNIME Java Snippet Nodes","bundleSymbolicName":"org.knime.jsnippets","bundleVendor":"KNIME AG, Zurich, Switzerland","featureSymbolicName":"org.knime.features.javasnippet","featureName":"KNIME Javasnippet","featureVersion":"5.12.0.v202606171353","featureVendor":"KNIME AG, Zurich, Switzerland","extensionId":"*hXfYFyb7ZzM0xseG"},"frequentSuccessors":[]}