How to use Regex Tool for data cleansing for multiple column in one go

[Kavita_22]

How to use Regex Tool for data cleansing for multiple column in one go ?

I need regex tool to do below activity

1. Replace with 0

2. Remove all leading space

[Yoshiro_Fujimori]

Hi @Kavita_22 ,

I would use Data Cleansing Tool.

Is there any reason you prefer RegEx tool?

[rzdodson]

Another approach to the Data Cleansing tool is a Multi-Field Formula tool (my preferred method). It allows for me to insert more complicated syntax, specifically Regex logic, in it without the need for an additional Formula tool after a Data Cleanse. 

[Kavita_22]

I have used Data Cleansing tool,
but in case error comes up related to selected column any where in process flow, the selected columns get unticked automatically from data Cleansing tool
and then after sorting the error, I am required to select the column again (the no. of columns to select is too high). So was searching for another method 

[Kavita_22]

I have used Data Cleansing tool,
but in case error comes up related to selected column any where in process flow, the selected columns get unticked automatically from data Cleansing tool
and then after sorting the error, I am required to select the column again (the no. of columns to select is too high). So was searching for another method 

[BS_THE_ANALYST]

@Kavita_22 

You'll need 2 multi-field formula tools. One to clean up your string columns and one to clean up your numeric columns. You can configure this easily by choosing the "type" in the multi-field formula tool's configuration + you can select "unknown" to make it dynamic and therefore less work. 

To remove trailing and leading spaces you'll need to use TRIM().

To replace nulls with 0s you'll just need an if statement like IIF(isempty([column here]), 0, [column here])

I see no need for regex here. That's for more complex string manipulation.

all the best,

BS