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.
Options
String Manipulation
Popular Successors
- Joiner8 %
- String Manipulation7 %
- String Manipulation (Multi Column)5 %
- GroupBy5 %
- String to Number5 %
- Rule Engine4 %
- String to Date&Time4 %
- Column Filter4 %
Row Filter (deprecated)3 %- Concatenate3 %
- Excel Writer3 %
- Rule-based Row Filter3 %
- Column Expressions2 %
- Missing Value2 %
- CSV Writer2 %
- Column Resorter2 %
- Duplicate Row Filter2 %
- Math Formula1 %
- Rule-based Row Splitter1 %
- Cell Splitter1 %
- Column Combiner< 1 %
- String Replacer< 1 %
- Math Formula (Multi Column)< 1 %
- Table Manipulator< 1 %
Row Splitter (deprecated)< 1 %- Column Appender< 1 %
- DB Writer< 1 %
- Sorter< 1 %
- Table Transposer< 1 %
- Column Aggregator< 1 %
- Column Renamer< 1 %
- Pivot< 1 %
- Constant Value Column< 1 %
- Column Renamer (Dictionary)< 1 %
- Loop End< 1 %
- Unpivot< 1 %
- Java Snippet< 1 %
- Column Merger< 1 %
- DB Insert< 1 %
- Table to JSON< 1 %
- Column Splitter< 1 %
- Column Rename (Regex)< 1 %
- Table Row to Variable Loop Start< 1 %
- Number to String< 1 %
- Missing Value Column Filter< 1 %
- Column List Loop Start< 1 %
- Column Name Extractor< 1 %
- Cross Joiner< 1 %
Cell Replacer< 1 %- Partitioning< 1 %
- Value Lookup< 1 %
- Reference Row Filter< 1 %
Regex Extractor< 1 %- Chunk Loop Start< 1 %
- String to Path< 1 %
- Catch Errors (Data Ports)< 1 %
- Column Auto Type Cast< 1 %
- Nominal Value Row Filter< 1 %
- Statistics< 1 %
CASE Switch Data (End)< 1 %- Tableau Writer< 1 %
- Group Loop Start< 1 %
- Cell Splitter By Position< 1 %
- Normalizer (PMML)< 1 %
- DB Update< 1 %
- Table Indexer< 1 %
- Double to Integer< 1 %
- Value Counter< 1 %
- Rule Engine (Dictionary)< 1 %
- CASE Switch End< 1 %
- End IF< 1 %
- Interactive Table (legacy)< 1 %
- Google Sheets Updater< 1 %
Table Row to Variable< 1 %Variable Condition Loop End< 1 %- Many to One< 1 %
Joiner< 1 %- RowID< 1 %
- GET Request< 1 %
Extract Date&Time Fields< 1 %- XLS Control Table Generator< 1 %
- Table Creator< 1 %
- Loop End (Column Append)< 1 %
- Column Comparator< 1 %
- Reference Column Filter< 1 %
- String Splitter (Regex)< 1 %
Column Rename< 1 %- Number Rounder< 1 %
Recursive Loop End (2 ports)< 1 %- Category to Number< 1 %
- Color Manager< 1 %
- Table View< 1 %
- DB Connector< 1 %
- DB Table Creator< 1 %
- Java Snippet (simple)< 1 %
- Date&Time Part Extractor< 1 %
- String to URI< 1 %
- Table Row to Variable< 1 %
Table Writer< 1 %- Case Converter< 1 %
- Constant Value Column Filter< 1 %
- Number to String (PMML)< 1 %
- CASE Switch Start< 1 %
- Extract Table Dimension< 1 %
Excel Sheet Appender (XLS)< 1 %- String to Date/Time (legacy)< 1 %
- Latitude/Longitude to Coordinate< 1 %
- XLS Conditional Formatter< 1 %
CSV Writer< 1 %- Table Writer< 1 %
- Recursive Loop End< 1 %
- Variable Loop End< 1 %
String To Number< 1 %- Domain Calculator< 1 %
String Replace (Dictionary)< 1 %- Data Explorer< 1 %
- Global Timer Info< 1 %
- Scatter Plot (legacy)< 1 %
- Parquet Writer< 1 %
- Counter Generation< 1 %
- Random Label Assigner< 1 %
- String Distances< 1 %
- Line Plot (Plotly)< 1 %
- String to Path (Variable)< 1 %
- Scatter Plot (JavaScript)< 1 %
- Python Script (legacy)< 1 %
- R Snippet< 1 %
- R View (Table)< 1 %
- Webpage Retriever< 1 %
- String to Duration< 1 %
- Write To Excel Template (deprecated)< 1 %
- Create Collection Column< 1 %
- Table Column to Variable< 1 %
Loop End< 1 %Recursive Loop Start (2 ports)< 1 %- Decision Tree Learner< 1 %
- Auto-Binner< 1 %
- Numeric Binner< 1 %
- Lag Column< 1 %
- Column to Grid< 1 %
- Row Splitter (Labs)< 1 %
- Missing Value (Apply)< 1 %
- Rank< 1 %
- String Replacer (Dictionary)< 1 %
- Top k Row Filter< 1 %
- Ungroup< 1 %
- Crosstab< 1 %
- Scatter Matrix (legacy)< 1 %
- Statistics View< 1 %
- SDF Writer< 1 %
- DB Loader< 1 %
- Bubble Chart (Plotly)< 1 %
Excel Writer (XLS)< 1 %Excel Writer (XLS)< 1 %- Java IF (Table)< 1 %
- String Matcher< 1 %
- Case Converter< 1 %
- Container Output (Table)< 1 %
- JSON Transformer< 1 %
- Date&Time to String< 1 %
- Date&Time to legacy Date&Time< 1 %
- UNIX Timestamp to Date&Time< 1 %
- Column to XML< 1 %
- XLS Background Colorizer< 1 %
- XLS Font Formatter< 1 %
- Configurable End IF/CASE< 1 %
- Append to Collection< 1 %
- XLS Coloured Writer< 1 %
- Variable to Table Column< 1 %
Table Row To Variable Loop Start< 1 %Database Delete< 1 %Generic Loop Start< 1 %- Recursive Loop Start< 1 %
- k-Means< 1 %
- Decision Tree Predictor< 1 %
- Linear Regression Learner< 1 %
- Category to Number (Apply)< 1 %
- One to Many< 1 %
- Linear Correlation< 1 %
- Table Validator< 1 %
- Table Validator (Reference)< 1 %
- Filter Apply Row Splitter< 1 %
- Nominal Value Row Splitter< 1 %
- Row Filter (Labs)< 1 %
- Reference Row Splitter< 1 %
- Normalizer< 1 %
- String to Number (PMML)< 1 %
- Reference Column Resorter< 1 %
- Row Aggregator< 1 %
- Row to Column Names< 1 %
- Row Sampling< 1 %
- Table Difference Finder< 1 %
- Rule Engine Variable< 1 %
- Rule-based Row Filter (Dictionary)< 1 %
- Numeric Outliers< 1 %
- Empty Table Switch< 1 %
- IF Switch< 1 %
- Extract Table Spec< 1 %
- Bar Chart< 1 %
Parquet Writer< 1 %- Molecule Type Cast< 1 %
- Microsoft SQL Server Connector< 1 %
- DB Delete (Table)< 1 %
- DB Merge< 1 %
- DB Transaction Start< 1 %
DB Table Creator< 1 %- Similarity Search< 1 %
- Violin Plot (Plotly)< 1 %
Microsoft Authenticator (Deprecated)< 1 %- Excel Reader< 1 %
- Send to Tableau Server< 1 %
- Dictionary Replacer< 1 %
- Dictionary Tagger (Multi Column)< 1 %
- Strings to Document< 1 %
- Path to String< 1 %
Google Authenticator (Deprecated)< 1 %- Column Selection Configuration< 1 %
- Scorer (JavaScript)< 1 %
- Table View (JavaScript)< 1 %
- Table Editor (JavaScript)< 1 %
- JSON Column Combiner< 1 %
- String to JSON< 1 %
- Columns to JSON< 1 %
- JSON to Table< 1 %
- MongoDB Connector< 1 %
- Data to Report (BIRT)< 1 %
- POST Request< 1 %
- Date&Time Difference< 1 %
- Date&Time-based Row Filter< 1 %
- Date&Time Shift< 1 %
- Modify Date< 1 %
- Modify Time< 1 %
- Modify Time Zone< 1 %
- Moving Aggregator< 1 %
- XML Writer< 1 %
- String to XML< 1 %
- XPath< 1 %
- Table to HTML String< 1 %
- Remove Empty Rows< 1 %
- Regex Extractor< 1 %
- String Similarity< 1 %
- Collection to String< 1 %
- Scorer< 1 %
- SVM Predictor< 1 %
- Create Bit Vector< 1 %
- Number to Category (Apply)< 1 %
- Reference Column Splitter< 1 %
- Interactive Pie chart (legacy)< 1 %
- Value Selection Widget< 1 %
- Split Collection Column< 1 %
- Extract System Properties< 1 %
- Set Operator< 1 %
- Cache< 1 %
- OSM Map View< 1 %
- Value Filter (legacy)< 1 %
- JSON Writer< 1 %
- JSON Path< 1 %
- CIR< 1 %
- Breakpoint< 1 %
- Create File Name (legacy)< 1 %
Table Column to Variable< 1 %Recursive Loop End< 1 %Recursive Loop Start< 1 %- Low Variance Filter< 1 %
CASE Switch Data (Start)< 1 %- Parallel Chunk Start< 1 %
- One Row to Many< 1 %
- Excel Cell Updater< 1 %
- RegEx Filter< 1 %
- Snowball Stemmer< 1 %
- String to Term< 1 %
- t-SNE (L. Jonsson)< 1 %
- JSON Row Combiner< 1 %
- Container Input (Row)< 1 %
Python Script (1⇒1)< 1 %- PUT Request< 1 %
- Table Row To Variable Run-for-time Loop Start< 1 %
- Empty Collection to Missing< 1 %
- Inject Column Properties< 1 %
- Python Snippet< 1 %
- String to Table< 1 %
- Wait...< 1 %
Table Row to Variable< 1 %- Read Images< 1 %
- X-Partitioner< 1 %
- Fuzzy Rule Learner< 1 %
- Fuzzy Rule Predictor< 1 %
- PNN Learner (DDA)< 1 %
- Polynomial Regression Learner< 1 %
- Numeric Scorer< 1 %
- PCA< 1 %
- Random Forest Predictor< 1 %
- Add Empty Rows< 1 %
Concatenate (Optional in)< 1 %- Binner (Dictionary)< 1 %
- Bootstrap Sampling< 1 %
- Edit Nominal Domain< 1 %
- Renderer to Image< 1 %
- Rule Engine Variable (Dictionary)< 1 %
- Kaplan-Meier Estimator< 1 %
- Conditional Box Plot (legacy)< 1 %
- Histogram (legacy)< 1 %
- Create Databricks Environment< 1 %
- Parallel Chunk End< 1 %
- MySQL Connector< 1 %
DB Loader< 1 %- One Rule Inserter< 1 %
- Mahalanobis Distance< 1 %
- K Nearest Neighbor (Distance Function)< 1 %
- Bar Chart (JFreeChart)< 1 %
- GroupBy Bar Chart (JFreeChart)< 1 %
Excel Reader (XLS)< 1 %Send to Power BI< 1 %- Java Snippet Row Filter< 1 %
- Bag Of Words Creator< 1 %
- Document Viewer< 1 %
- Create Temp Folder< 1 %
- Google Sheets Appender< 1 %
- Column Filter Configuration< 1 %
- Column Filter (legacy)< 1 %
- Column Selection (legacy)< 1 %
- Line Plot (JavaScript)< 1 %
- Tile View (JavaScript)< 1 %
- Object Inserter< 1 %
- Window Loop Start< 1 %
- Date Field Extractor (legacy)< 1 %
- BERT Predictor< 1 %
- Hash Calculator< 1 %
- HTTP Retriever< 1 %
- Extract Text< 1 %
Views
- This node has no views
Workflows
00_xplan sf dbconv-insert via xport-AccountKNIME Hub
01_AnonymizerKNIME Hub- 01_BERT_Sentiment_AnalysisKNIME Hub
- 01_Clickstream_AnalysisKNIME Hub
01_xplan sf dbconv-insert via csv-AddressKNIME Hub- Show all 617 workflows
Links
- Replace Strings that represent null in complete tableby ipazin on 2022-11-15forum.knime.com/p/175252
- How to remove % sign in KNIMEby Sajjadmosaheb on 2020-09-06forum.knime.com/p/101776
- Delete specific characters from multiple columnsby HansS on 2021-10-11forum.knime.com/p/136688
- Selenium Nodes: How to scroll within a section?by qqilihq on 2021-03-20forum.knime.com/p/119029
- How to join the columns on the same RowID Inside a Column List Loop?by HansS on 2020-10-15forum.knime.com/p/105729
Developers
You want to see the source code for this node? Click the following button and we’ll use our super-powers to find it for you.