REG_EX Match formula vs REG_EX Parse tool

[adamp]

I've got a fairly simple regex statement and I'm intending to use it to filter some data. I want to separate rows based on whether or not a text field contains two numbers followed by an apostrophe (e.g., 40')

I'm using the regular expression \d{2}' and it test just fine. It also works when I put it into the RegEx Parse tool... I can see a new variable with the expected values from the original string.

But, when I put that same regular expression into a formula on a filter tool it only returns nulls: (REGEX_Match([Material Description],"\d{2}'")

Can someone enlighten me about what's going on here and if/how I might get this working as a formula in the filter tool to avoid adding additional tools to my workflow?

[PaulN]

Hi @adamp,

Thank you for posting!

I can't seem to be able to recreate your issue. Could you please check my example below and let me know what is different in your case ?

Kind regards,

Paul Noirel

Sr Customer Support Engineer, Alteryx

[adamp]

@PaulN Your test looks like what I'm doing and only the results are different. I've attached the offending part of the workflow for you as well as a screen shot.

[PaulN]

Hey @adamp,

Many thanks for the clarification!

So the result is expected:

In my case I have configured RegEx tool with output method set to Match which is the equivalent of REGEX_Match.

On your screenshot, I can see that the tool is set to Parse so it's going to use the regex to extract any matching pattern. This explains why "1" will not return any results while "10' DELUXE DOORS" actually returns "10'" which is a match for \d{2}'.

I hope this helps.

Thanks,

Paul Noirel

Sr Customer Support Engineer, Alteryx

[adamp]

@PaulN What I'm really looking for is understanding why the REGEX_MATCH in the filter isn't finding anything. I only setup the Parse as a check on my regular expression. I understand that the Parse configuration is slightly different and putting the parenthesis changes the regular expression slightly. Any thoughts on why \d{2}' isn't matching anything in the filter tool?

[Nate1]

Hi @adamp

Your filter tool doesn't have a condition in it. You need to add an = to the filter. Here is some update regex that accounts for the possibility of text before or after the match you are looking for.

REGEX_Match([Material Description],".*\d{2}'.*") = -1

[PaulN · Accepted solution]

Hi @adamp,

The reason of the behaviour, as shown in @Nate1 example, is because your regex will only capture patterns like "10'" but not "10' DELUXE DOORS". You could see your expression as "^\d{2}'$" if you prefer. If you expect characters before or after, you will need to use the following expression :

REGEX_Match([test], ".*\d{2}'.*")

Note that as REGEX_Match() already returns a boolean, there is no need of a operator like "=" or "!=".

Thanks and thanks to @Nate1,

Paul Noirel

Sr Customer Support Engineer, Alteryx

[adamp]

I knew it was probably something simple! Thanks. @PaulN @Nate1

I guess instinctively I expected the Match function to already expect and/or ignore superfluous items in the string and just look for the token I specify. One day I'll be practiced enough (I hope) to remember ~ I ~ have to account for the full string in addition to the token I'm looking for.

[hannahhanigan]

I'm having a similar issue. I have a set of numbers, some of which have leading and trailing numbers that I need removed. Basically, if the item number column begins with a 6, I want to remove the first two numbers and the last 1 number, but if the number begins with anything else, I want to leave it alone. I'm pretty confident that I need to use the RegEx tool, but not sure how to do an "IF" type function in association with doing a fixed width. 

[PaulN]

Hi @hannahhanigan,

If I understand your case correctly, a simple formula with text functions will do the trick:

if Left(ToString([Item Number]),1) == "6" then
   ToNumber(Substring(ToString([Item Number]),3,Length(ToString([Item Number]))-4))
else
   [Item Number]
endif

I have assumed that "Item Number" was a numeric, hence multiple ToString() functions. 

To sum up:

if first character is "6" then return a sub string starting at the 3rd character and of a length of original item number minus 1 character and minus the original position.

Thanks,

Paul Noirel

Sr Customer Support Engineer, Alteryx