Regex Optional Group

[Jbone]

Hello,

I'm trying to use regex to parse some characters from an order number string. If the order number ends in '00' or '01' I want to parse the string into Order Prefix and Order Suffix fields

Order Prefix                  Order Suffix

73528489802                01

72584895012                00

If the order number ends in something other than '00' or '01', I want the entire order number in the Order Prefix field and Order Suffix to be null

Order Prefix                  Order Suffix

7353964335239            null

Using (.*)(00|01)$ I get the desired results if the order number ends in '00' or '01'...other wise both regex outputs are null

If I make the second group optional using (.*)(00|01)?$ the entire order number shows up in the first output group. 

Order Prefix                  Order Suffix

7352848980201            blank

7258489501200            blank

7353964335239            blank

I've attached an example workflow. 

Data:

Order Number
7352848980201
7258489501200
7353964335239
8011724161244
W73368203492
8005687663201
8015624961200
8015624961299
8020874081211
A020896796201
8020896796201

[BrandonB · Accepted solution]

Hi @Jbone 

You can use the first regular expression that you made with a formula tool directly following. 

Take a look at the attached workflow and let me know if this takes care of your issue!

-Brandon

[RobertOdera]

HI @Jbone 

Try Formula Tool, where

Order Prefix given by

IF REGEX_Match([Order Number], '(^.*)(00|01)$') =-1
THEN Null()
ELSE [Order Number]
ENDIF

Order Suffix given by

IF REGEX_Match([Order Number], '(^.*)(00|01)$') =-1
THEN RIGHT([Order Number],2)
ELSE Null()
ENDIF

[Jbone]

Thanks for the quick reply @BrandonB and @RobertOdera . Using a formula tool did cross my mind, but I was thinking there was a way to accomplish my goal using just regex tool. This is simply enough though.

Thanks!

[RobertOdera]

@Jbone, you are most welcome.

Yes, I also tried just the regex tool, but given the outputs that you wanted (what goes into or not) for Prefix & Suffix, I figured a formula tool was necessary...

Thanks for the question!