Replace Value multiple columns (Exact Matches Only)

Hi there!

I am trying to replace all “-” out of multiple columns in my dataset.
I know that I am to use String Manipulation (Multi Column) and I am using this formula…
replace($$CURRENTCOLUMN$$, “-”, “”, “w”)

But it is not searching for Exact Matches.
Meaning it is also changing my data that has eg. “Planner - No” to “Planner No”.

I am only looking to change all Exact Matches, entire Cell = “-” to change to blank cell.

Can someone share some advice? Thanks!!

Hi @neekstressed

A regexReplace gives you a bit more control over this.

Assume this input:

image

If you then use regexReplace($$CURRENTCOLUMN$$,"^-$","")
Rows that only contain a - are then converted to a blank.

2 Likes

Hi @ArjenEX ,

Thank you so much!
I was trying to figure our the regex language but was struggling ):
Thanks for sharing!

I managed to change some columns but I have this error for certain Columns.
ERROR String Manipulation (Multi Column) 4:286 Execute failed: Execution stopped: Evaluation of expression failed for row “Row0”: java.lang.NullPointerException

Do you why I would run into such an error?

First suspicion would be the data type of those columns not equal to string but that’s very hard to tell without seeing your data and or workflow :wink:

Hi @ArjenEX !
I figured out the error – it was due to “missing values” and I resolved it by using a Missing Value node before the String Manipulation Node to resolve all missing values before running the regexReplace($$CURRENTCOLUMN$$,“^-$”,“”) to convert all exact cell matches “-” to blank.

Thanks for your help!

Hi @neekstressed , you don’t need to use the Missing Value node to convert the missing values into empty string - the Missing Value node is quite slow, because of PMML.

You can just use the toEmpty() function in your expression. You can use it like this:
regexReplace(toEmpty($$CURRENTCOLUMN$$), "^-$", "")

3 Likes

Hi @bruno29a , thanks so much! It works!

Can you explain a little on the expression?
regexReplace(toEmpty($$CURRENTCOLUMN$$), “^-$”, “”)

I understand toEmpty same as ismissing – so wouldnt it mean that we want the expression “to replace, empty current columns” ?
Do you know how I can better understand Regex language?

Thanks!

Interesting to see that string manipulation does not throw errors with missing values but string manipulation (Multicolumn) does

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