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 Predecessors
- String Manipulation (Multi Column)6 %
- String Manipulation6 %
- Column Filter6 %
Row Filter (deprecated)5 %- Excel Reader4 %
- Number to String4 %
- CSV Reader3 %
- GroupBy3 %
- Joiner3 %
Column Rename3 %- Rule-based Row Filter3 %
- Rule Engine3 %
- Missing Value3 %
- Concatenate2 %
- DB Query Reader2 %
- Date&Time to String2 %
- Cell Splitter2 %
- Column Resorter1 %
- Auto-Binner1 %
- String to Number1 %
- Table Creator1 %
- Math Formula1 %
- Rule-based Row Splitter< 1 %
- Duplicate Row Filter< 1 %
- String Replacer< 1 %
- String to Date&Time< 1 %
- Column Expressions< 1 %
- Constant Value Column< 1 %
Row Splitter (deprecated)< 1 %- Column Renamer< 1 %
- File Reader< 1 %
- Sorter< 1 %
- Column Splitter< 1 %
- Math Formula (Multi Column)< 1 %
- DB Reader< 1 %
- Column List Loop Start< 1 %
- Table Row to Variable< 1 %
- Loop End< 1 %
- Table Transposer< 1 %
- Number Rounder< 1 %
- Column Combiner< 1 %
- Table Manipulator< 1 %
- Column Rename (Regex)< 1 %
- Reference Row Filter< 1 %
- Column Aggregator< 1 %
- Column Name Extractor< 1 %
- Cross Joiner< 1 %
- Date&Time Part Extractor< 1 %
- XPath< 1 %
- String Splitter (Regex)< 1 %
- Pivot< 1 %
- RowID< 1 %
- Column Renamer (Dictionary)< 1 %
- Missing Value Column Filter< 1 %
- Column Appender< 1 %
Joiner< 1 %- Cell Splitter By Position< 1 %
- Ungroup< 1 %
- Double to Integer< 1 %
- Nominal Value Row Filter< 1 %
- Parallel Chunk End (multi port)< 1 %
- Column Auto Type Cast< 1 %
- Number to String (PMML)< 1 %
- Loop End (Column Append)< 1 %
Regex Extractor< 1 %- Java Snippet< 1 %
- Table Reader< 1 %
Counting Loop Start< 1 %- Regex Extractor< 1 %
- Row to Column Names< 1 %
- JSON Path< 1 %
- Case Converter< 1 %
- Legacy Date&Time to Date&Time< 1 %
CSV Reader< 1 %- Variable to Table Row< 1 %
- Partitioning< 1 %
- Catch Errors (Data Ports)< 1 %
- Value Lookup< 1 %
- Fixed Width File Reader< 1 %
- Linear Correlation< 1 %
- Column Comparator< 1 %
- Shuffle< 1 %
- Variable Expressions< 1 %
- Google Sheets Updater< 1 %
- Date&Time Difference< 1 %
Database Reader< 1 %- Variable Creator< 1 %
- Table Row to Variable Loop Start< 1 %
- Container Input (JSON)< 1 %
- JSON to Table< 1 %
- Modify Time Zone< 1 %
- Generic Loop Start< 1 %
- String to Number (PMML)< 1 %
- Chunk Loop Start< 1 %
- Column Merger< 1 %
- Row Sampling< 1 %
Excel Reader (XLS)< 1 %- Collection to String< 1 %
- Try (Data Ports)< 1 %
- Variable to Table Column< 1 %
- Parquet Reader< 1 %
- DB Writer< 1 %
- Document Data Extractor< 1 %
- Python Script (legacy)< 1 %
- RDKit Canon SMILES< 1 %
File Reader< 1 %- One to Many< 1 %
Extract Date&Time Fields< 1 %- Modify Date< 1 %
- Breakpoint< 1 %
- Variable Loop End< 1 %
- Numeric Binner< 1 %
- Column to Grid< 1 %
- Row Filter (Labs)< 1 %
- Rule Engine (Dictionary)< 1 %
- Numeric Outliers< 1 %
- Statistics< 1 %
- CASE Switch End< 1 %
- CASE Switch Start< 1 %
- IF Switch< 1 %
- DB SQL Executor< 1 %
- Java Snippet (simple)< 1 %
- Path to String (Variable)< 1 %
- Column Filter Configuration< 1 %
- String Input (legacy)< 1 %
- Date&Time-based Row Filter< 1 %
- Date Field Extractor (legacy)< 1 %
- String Replacer (Dictionary)< 1 %
- Split Collection Column< 1 %
Variable to Table Row< 1 %Loop End< 1 %Cell Replacer< 1 %- Reference Column Filter< 1 %
- Unpivot< 1 %
- Merge Variables< 1 %
- Counter Generation< 1 %
- Google Sheets Reader< 1 %
- Python Source (legacy)< 1 %
- Wait...< 1 %
- Table Column to Variable< 1 %
- File Reader (Complex Format)< 1 %
- Counting Loop Start< 1 %
- Group Loop Start< 1 %
- Recursive Loop End< 1 %
- Recursive Loop Start< 1 %
Recursive Loop Start< 1 %- Add Empty Rows< 1 %
- Category to Number< 1 %
- Lag Column< 1 %
- Table Validator< 1 %
- Table Validator (Reference)< 1 %
- Domain Calculator< 1 %
- Constant Value Column Filter< 1 %
- Nominal Value Row Splitter< 1 %
- Row Splitter (Labs)< 1 %
- Reference Row Splitter< 1 %
- Normalizer< 1 %
- Missing Value (Apply)< 1 %
- Reference Column Resorter< 1 %
- String Manipulation (Variable)< 1 %
String Replace (Dictionary)< 1 %- Table Difference Finder< 1 %
- Value Counter< 1 %
- Empty Table Switch< 1 %
- End IF< 1 %
CASE Switch Data (Start)< 1 %- Color Manager< 1 %
- String Matcher< 1 %
- List Files/Folders< 1 %
- Path to String< 1 %
- Single Selection Configuration< 1 %
- Table View (JavaScript)< 1 %
- Salesforce Simple Query< 1 %
- SPARQL Query< 1 %
- Date&Time Shift< 1 %
- Modify Time< 1 %
- String to Date/Time (legacy)< 1 %
- Create Table Structure< 1 %
- Data Explorer< 1 %
- Top k Row Filter< 1 %
- Time to String (legacy)< 1 %
- Rank< 1 %
- Line Reader< 1 %
- Don't Save Start< 1 %
- RDKit Kekulizer< 1 %
- Extract Variables (Data)< 1 %
- XLS Control Table Generator< 1 %
- Table Editor (JavaScript)< 1 %
- ARFF Reader< 1 %
- Tika Parser< 1 %
- Table to JSON< 1 %
- Remove Empty Rows< 1 %
IF Switch (Flow Variable Value)< 1 %- Elemental Analysis< 1 %
- Inject Variables (Data)< 1 %
- Java Snippet Row Splitter< 1 %
Table Row to Variable< 1 %Database Connection Table Reader< 1 %Table Reader< 1 %Interval Loop Start< 1 %- Numeric Scorer< 1 %
CASE Switch Data (End)< 1 %- Empty Table Creator< 1 %
- Read Excel Sheet Names< 1 %
- Date&Time Input (legacy)< 1 %
- Date&Time to legacy Date&Time< 1 %
- UNIX Timestamp to Date&Time< 1 %
- Time Field Extractor (legacy)< 1 %
Simple File Reader< 1 %- Copy/Move Files (legacy)< 1 %
- Java Edit Variable< 1 %
Generic Loop Start< 1 %Recursive Loop Start (2 ports)< 1 %- Auto-Binner (Apply)< 1 %
- Target Shuffling< 1 %
- Rule Engine Variable< 1 %
- Capture Workflow Start< 1 %
- SDF Reader< 1 %
- Java Edit Variable (simple)< 1 %
- PDF Parser< 1 %
- String to Path< 1 %
- Container Input (Table)< 1 %
- Create Date&Time Range< 1 %
- Extract Attribute< 1 %
- Data Generator< 1 %
- Hash Calculator< 1 %
- Advanced MolConverter< 1 %
- Bi Reactor< 1 %
- logP< 1 %
- Create Collection Column< 1 %
- Files to Binary Objects< 1 %
- URI to String< 1 %
- URL to File Path< 1 %
Recursive Loop End (2 ports)< 1 %- SMOTE< 1 %
- Random Forest Predictor< 1 %
- Many to One< 1 %
- Correlation Filter< 1 %
- Equal Size Sampling< 1 %
- Filter Apply< 1 %
- Cache< 1 %
- Extract Table Spec< 1 %
- Timer Info< 1 %
- SDF Extractor< 1 %
- DB Connection Closer< 1 %
- DB Insert< 1 %
- Similarity Search< 1 %
- Stop Word Filter< 1 %
- Document Vector< 1 %
- String Configuration< 1 %
- Column Selection Configuration< 1 %
- Multiple Selection Configuration< 1 %
- GET Request< 1 %
- Duration to Number< 1 %
- Moving Aggregator< 1 %
- Moving Average< 1 %
- RDKit To Molecule< 1 %
- Find Elements< 1 %
- Quit WebDriver< 1 %
Table Extractor< 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 620 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.