RegEx replacing nulls with empties - why?
- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Mute
- Printer Friendly Page
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
Can anyone explain why this regex replace expression would turn nulls into empties? Pictures attached too.
regex_replace([Field1], '\s+', ' ')
Thanks in advance!
Solved! Go to Solution.
- Labels:
- Regex
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
Thanks for responding. Yes, it's an odd behavior. Will have to add the workaround!
