# Replacing strings in a column based on a reference table

**URL:** <https://forum.knime.com/t/replacing-strings-in-a-column-based-on-a-reference-table/74980>\
**Category:** KNIME Analytics Platform\
**Created:** [November 3, 2023, 5:38pm UTC](https://forum.knime.com/t/replacing-strings-in-a-column-based-on-a-reference-table/74980 "2023-11-03T17:38:28Z")\
**Posts on this page:** 6\
**Page:** 1

<div class="post-metadata">

**Author:** ![mac95](https://forum-cdn.knime.com/letter_avatar_proxy/v4/letter/m/a5b964/32.png) [@mac95](https://forum.knime.com/u/mac95)\
**Post date:** [November 3, 2023, 5:38pm UTC](https://forum.knime.com/t/replacing-strings-in-a-column-based-on-a-reference-table/74980/1 "2023-11-03T17:38:28Z")

</div>

Hi,

I’m looking to replace all instances of several strings in a column. Is there a way to quickly to this by referencing a table? E.g., a table like this:

![image](https://forum-cdn.knime.com/uploads/default/original/3X/e/b/ebbe8614ea0ff727b91e7a4c269f95115b755307.png)

would transform these values:

**Orange** juice  
**Lemon** pie  
**Chicken** strips  
**Turkey** stuffing  
**Broccoli** soup  
**Tomato** sauce

into:

**Citrus** juice  
**Citrus** pie  
**Poultry** strips  
**Poultry** stuffing  
**Green** soup  
**Red** sauce

Many thanks!

---

<div class="post-metadata">

**Author:** ![hmfa](https://forum-cdn.knime.com/letter_avatar_proxy/v4/letter/h/9fc29f/32.png) [@hmfa](https://forum.knime.com/u/hmfa)\
**Post date:** [November 3, 2023, 6:16pm UTC](https://forum.knime.com/t/replacing-strings-in-a-column-based-on-a-reference-table/74980/2 "2023-11-03T18:16:19Z")

</div>

Hi @mac95

Have a look at this

> **[String Replacer (Dictionary)](https://hub.knime.com/knime/extensions/org.knime.features.base/latest/org.knime.base.node.preproc.stringreplacer.dict2.StringReplacerDictNodeFactory)**
>
> This node replaces strings in selected columns. The dictionary table provides the patterns to search for and the values to replace matches with. If no pattern …

Br

---

<div class="post-metadata">

**Author:** ![mac95](https://forum-cdn.knime.com/letter_avatar_proxy/v4/letter/m/a5b964/32.png) [@mac95](https://forum.knime.com/u/mac95)\
**Post date:** [November 6, 2023, 4:41pm UTC](https://forum.knime.com/t/replacing-strings-in-a-column-based-on-a-reference-table/74980/3 "2023-11-06T16:41:41Z")

</div>

Thanks, @hfma.

Unfortunately, I can only get this to work if the cell value **equals** one of my dictionary values - not if it **contains** one of these values. So, any cell equal to “Orange” is replaced with “Citrus”, but any cell equal to “Orange juice” is unchanged.

Do you know if this node capable of replacing sub-strings?

Best,

M

---

<div class="post-metadata">

**Author:** ![gonhaddock](https://forum-cdn.knime.com/user_avatar/forum.knime.com/gonhaddock/32/35425_2.png) [@gonhaddock](https://forum.knime.com/u/gonhaddock)\
**Post date:** [November 6, 2023, 5:20pm UTC](https://forum.knime.com/t/replacing-strings-in-a-column-based-on-a-reference-table/74980/4 "2023-11-06T17:20:29Z")

</div>

Hello @mac95

For a literal match; I would suggest to loop over your reference table with ‘Table Row To Variable Loop Start’.  
Connect your loop variable port to your data through a String Manipulation node, with the replace(str, search, replace) function, then you can close the loop with a Loop End node.

BR

---

<div class="post-metadata">

**Author:** ![hmfa](https://forum-cdn.knime.com/letter_avatar_proxy/v4/letter/h/9fc29f/32.png) [@hmfa](https://forum.knime.com/u/hmfa)\
**Post date:** [November 6, 2023, 5:28pm UTC](https://forum.knime.com/t/replacing-strings-in-a-column-based-on-a-reference-table/74980/5 "2023-11-06T17:28:20Z")

</div>

Hi @mac95

Yes it does. It depends on your configuration.  
Try this workflow

[String replacer.knwf](https://forum.knime.com/uploads/short-url/r4x1fOvONRrQSkqjNCUVwzemSLV.knwf) (74.4 KB)

Input table  
 ![image](https://forum-cdn.knime.com/uploads/default/original/3X/3/7/37f6a229f70e75025692c60c14f39af77fe1e6e5.png)  
Disctionary  
 ![image](https://forum-cdn.knime.com/uploads/default/original/3X/c/f/cf858a14de931c8deb839f02917ec31874c418d4.png)  
Node configuration

 ![image](https://forum-cdn.knime.com/uploads/default/original/3X/1/9/197dcfa2b9081bfc0959d6be2320fd7f1b095145.jpeg)  
Result  
 ![image](https://forum-cdn.knime.com/uploads/default/original/3X/e/6/e68c64d34ac13ac50834422750684b5f10aaf5a9.png)

Br

---

<div class="post-metadata">

**Author:** ![system](https://forum-cdn.knime.com/uploads/default/original/1X/ab3ccf34482a0329361734a18199390177204f15.png) [@system](https://forum.knime.com/u/system)\
**Post date:** [February 4, 2024, 5:29pm UTC](https://forum.knime.com/t/replacing-strings-in-a-column-based-on-a-reference-table/74980/6 "2024-02-04T17:29:04Z")

</div>

This topic was automatically closed 90 days after the last reply. New replies are no longer allowed.