Hub
  • Software
  • Blog
  • Forum
  • Events
  • Documentation
  • About KNIME
  • KNIME Hub
  • Nodes
  • String Manipulation (Multi Column)
NodeNode / Manipulator

String Manipulation (Multi Column)

Manipulation Column Convert & Replace Streamable
Drag & drop
Like
Copy short link

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.

Node details

Input ports
  1. Type: Table
    Input data
    Input data with multiple columns to proces.
Output ports
  1. Type: Table
    Output data
    Input data with replaced or appended columns.

Extension

The String Manipulation (Multi Column) node is part of this extension:

  1. Go to item

Related workflows & nodes

  1. Go to item
    justknimeit - 13 - Raffaello Barri
    lelloba > Public > Just Knime it! > justknimeit - 13 - Raffaello Barri
  2. Go to item
    KNIME_Challenge13_Transactions
    lenexa_jayhawk > Public > KNIME_Challenge13_Transactions
  3. Go to item
    String Manipulation (Multi Column)
    ETL Data manipulation String manipulation
    +1
    This workflow shows three different string manipulation on multiple columns, namely: - ca…
    knime > Examples > 02_ETL_Data_Manipulation > 04_Transformation > 04_StringManipulation_MultiColumn
  4. Go to item
    Table Difference Finder
    ETL Data manipulation String manipulation
    +1
    This workflow shows the usage of Table Difference Finder node. A csv file is read in. The…
    knime > Examples > 02_ETL_Data_Manipulation > 04_Transformation > 05_TableDifference_Finder
  5. Go to item
    jKi-13
    #justknimeit
    There has been no description set for this workflow's metadata.
    cf_123 > Public > jKi-13
  6. Go to item
    Regex to Extract Time Information
    Regex Split Time calculation Multi Column
    This workflow uses Regex Split node with optional capturing groups to extract time inform…
    ipazin > Public > 2020_07_20_Regex_to_Extract_Time_Information
  7. Go to item
    Header Rename with Regex
    eamendola > Public > Header Rename with Regex
  8. Go to item
    Split Cells and Create New Rows
    kathrin > Forum Questions > Split Cells and Create New Rows
  9. Go to item
    KNIME_challenge_ justknimeit-13
    Justknimeit-13
    There has been no description set for this workflow's metadata.
    johnpfisk > Public > justknimeit > KNIME_challenge_ justknimeit-13
  10. Go to item
    Removing Special Words from Multiple Columns
    Data Manipulation Data Cleaning Multi Column
    +2
    This workflow demonstrates how to remove i.e. clean multiple columns removing all words l…
    ipazin > Public > 2020_09_17_Removing_Special_Words_from_Multiple_Columns

No known nodes available

KNIME
Open for Innovation

KNIME AG
Hardturmstrasse 66
8005 Zurich, Switzerland
  • Software
  • Getting started
  • Documentation
  • E-Learning course
  • Solutions
  • KNIME Hub
  • KNIME Forum
  • Blog
  • Events
  • Partner
  • Developers
  • KNIME Home
  • KNIME Open Source Story
  • Careers
  • Contact us
Download KNIME Analytics Platform Read more on KNIME Server
© 2022 KNIME AG. All rights reserved.
  • Trademarks
  • Imprint
  • Privacy
  • Terms & Conditions
  • Credits