Regex_Replace seems to behave incorrectly

Hi,

I have a field with some data in it for example

[Location]="MSY-MAINT/BAY/1"

The location field should only ever have letters, numbers, the "-" symbol or the "/" symbol and I am willing to take that risk of user error to not have the Regex ridiculously complex. The underlying ERP does a good amount of validation already.

I was trying to do a formula to capture only up to the first "/" so the desired result would be "MSY-MAINT"

This is the 4th formula I started building up after the other methods became too complex to be worth debugging. Note that I've added several qualifiers that appear to be necessary per Regex101, but adding them didn't change the behavior of the Alteryx formula. It did the same thing with or without them (this includes the + for multiple matches and the \ as an escape character).

REGEX_Replace(Uppercase([Location), "([0-9A-Za-z]+)\/(.*)","$1")

This is returning "MSY-MAINT" but it should only return "MSY" as the "-" symbol is not in the capture group. "-" is ascii code 45. Verified with RegEx101 the capture group does not include ascii 45.

regex101: build, test, and debug regex

This should be required for the result Alterxy gave to be correct of "MSY-MAINT"

REGEX_Replace(Uppercase([Locaiton]), "([0-9A-Za-z\-]+)\/(.*)","$1")

I'm sure it's something I misunderstand about Regex or the implemenation of it in Alteryx. Does anyone know why Alteryx appears to give the incorrect result for the first version of the formula?

Thanks.

Regex

Accepted answers

Pilsner

Hello @ColinG

Great spot, this does appear to be quite a tricky case. I have investigated a little bit, and I believe I have found an answer.

The regex you provided ([0-9A-Za-z]+)\/(.*) actually matches the string "MSY-MAINT/BAY/1" as follows:

([0-9A-Za-z]+) matches "MAINT"
\/ matches "/"
(.*) matches "BAY/1"

As you will notice, "MSY-" is effectively left unmatched. This suggests that the regex replace is also keeping unmatched characters, hence your result is both the unmatched "MSY-" and the matched "MAINT". Admittedly, when using regex from within the formula tool, this is hard to notice, but when I instead used the regex tool itself, this became apparent by toggling the "Copy unmatched text to output option". Please see the images below:

With Copy unmatched text enabled:

With Copy unmatched text disabled:

The reason this is happening is because, like you correctly identified, the character set [0-9A-Za-z] does not include the "-". and you have specified, that immediately after the character set, should be a "/". Therefore the regex statement starts looking at the "M" after the "-".

One way to get around this would be to change the "\/" to a "\b". In Regex, "\b" defines any word boundary, so the regex will now see both the "-" and "/" as word boundaries, returning you the value "MSY". The full regex statement would be: ([0-9A-Za-z]+)\b(.*)

I have attached the workflow with my examples below.

I hope the above all makes sense. Please let me know how you get on.

Regards - Pilsner

New Workflow1.yxmd

All comments

Pilsner

With Copy unmatched text disabled:

New Workflow1.yxmd

ColinG

I didn't understand what you were saying at first and tend to inherently distrust Alteryx's frequently not quite standard implementation of tools. But this seems to be me not understanding how Regex works under the covers. I should have tried a full validation in Regex101. It confirms what you said and how the RegEx tool worked.

I believed Regex would pattern match from the beginning of a string so I would have expected it to find MSY and stop at the "-". But it appears Regex really finds the end of the first grouping, and then starts pattern matching from the end of the group "T" to the first nonmatching letter (the "-" after the "M") and drops the rest of the leading string.

Not having "-" in the group it stopped at the "-"

R101 step 1.png

Having "-" in the group it continues to the beginning of the string

R101 step 2.png

It would have helped me realize the error of my assumption if Regex_Replace behaved more like standard RegEx, or had a parameter value that specifically called out that copying unmatched characters was a configurable default.

Using \b is an inventive alternative I always forget about trying.

Thanks.

Pilsner

You're absolutely right, regex by default matches the full pattern wherever that may be and will only start at the beginning of the line if specifically told to using the "^" symbol.

I too, found it confusing that there was no parameter for "copy unmatched text" within the Regex_Replace function in the formula tool. I have added it as an idea to the community here , so hopefully this will be added at some point.

Glad I could help.

Regards - Pilsner

ColinG

I accept the premise though it does still seem like Regex is behaving differently using ([0-9A-Za-z]+)\/(.*) since it is not forcing a full pattern match from delimiter working back to the beginning of the text input (as far as not returning anything if there isn't a full match).

The only way I can reconcile this discrepancy logically is that Regex treats the given delimiter "/" as a hard terminator to the pattern match, whether it starts from the end or starts from the beginning, whereas the beginning of the string is not treated as an equivalent hard terminator to assess the full pattern match. It just stops because the pattern doesn't continue, and since a terminator of a prior group wasn't found, it is treated as a 100% match and returned.

For human logic this is a little wonky, but isn't Regex anyway...

Thanks for the pointers. This has definitely helped me understand some of the finer points of Regex use and debugging.

Pilsner

I agree, sometimes the logic can seem a bit weird from a human perspective. Whenever I think I understand regex, another curveball will appear.

Glad I could help 😊

Quick Links

This months top contributors

atcodedog05 19598

Qiu 15867

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703