How do I remove trailing and leading whitespace in a prepare recipe?

Noah

I want to turn ' AAAA 123 BBB CCCCC ' into 'AAAA 123 BBB CCCCC'

I tried find and replace with this:

(\s\s+)

but it turns it to 'AAAA123BBBCCCCC'

Turribeach

In a Prepare recipe the output column name can be the same as the column you are modifying (see sample below). Once you have step done on one column you can click on the three dots and select Duplicate step. Once you have 4/5 steps you can select them and click on the Actions menu to Copy/Paste them all at once. Then modify the steps directly on the Script section. Finally you can select them all and group them in theAcitons menu to make your Prepare recipe more manageable.

Turribeach

Use the strip() formula function (also called trim()):

https://doc.dataiku.com/dss/12/formula/index.html#string-functions

Noah

https://community.dataiku.com/discussion/comment/44153#Comment_44153

Thank you kindly for your answer. Is there a way to do this on 50 columns ? I want to keep the original names.

Noah

https://community.dataiku.com/discussion/comment/44155#Comment_44155

Thank you. I knew about that route, but was hoping there was a catch all.

One concern I have is that TRIM and STRIP do not work on Numbers. So most of my values are A1 or AAA but a few are 123 , and those are returning as blanks. I ended up wrapping it in a Coalesce.

Also in the past DSS had some weird errors when I tried to rewrite into the same column. I thought currently we had to write as new column, delete old and then rename.

Turribeach

Actually there is a way to do it for all columns, your other post made me find it. Add a new step in a Prepare recipe and select the Transform string processor. Under column select "all" and under mode select "Remove leading/trailing whitespaces".