Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Alteryx Designer Desktop Discussions

Find answers, ask questions, and share expertise about Alteryx Designer Desktop and Intelligence Suite.
SOLVED

RegEx replacing nulls with empties - why?

G1
8 - Asteroid

Can anyone explain why this regex replace expression would turn nulls into empties? Pictures attached too.

 

regex_replace([Field1], '\s+', ' ')

 

Thanks in advance!

 

part1.PNGpart2.PNG

3 REPLIES 3
mpennington
11 - Bolide

Sometimes the RegEx match and replace formulas can be a bit tricky to get to behave exactly as you would intend.  In this case, I would recommend just adding a test for nulls.

 

IIF(IsNull([Field1]),Null(), REGEX_Replace([Field1], '\s+', '')

 

I think the output of a RegEx  formula wants to be a string, so that may override Nulls, if I am correct.   Not entirely sure, but an easy workaround. 

apathetichell
18 - Pollux

I believe "" or empty is the default for an unmatched text regex_replace in the formula tool - so since nothing is done/the formula returns "" - it puts "" in the cell.

 

If you do a regex_count for your same formula you'll see that it isn't recognizing the null() field as "\s+"

 

As a workaround you can do an if [field1]=null() then null() else regex... or the cooler iif as recommended by @mpennington 

 

 

G1
8 - Asteroid

Thanks for responding. Yes, it's an odd behavior. Will have to add the workaround!

Labels