Alteryx designer Discussions

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

Regex Expression not behaving as expected/desired

Highlighted
ACE Emeritus
ACE Emeritus

I have been trying to get a Regex expression to work and just can't seem to figure out the correct syntax. Rather than waste more time, I'm opening it up to the Community...

 

Sample Records:

EAG_CAN_2017_Apples1_A

EAG_CAN_2018_Oranges15_B

 

Desired Output:

EAG_CAN_2017_Apples_A

EAG_CAN_2018_Oranges_B

 

Syntax I've tried:

Regex_Replace([Field],"(.*)\d+(.*)","$1"+"$2")

Regex_Replace([Field],"(.*)\d{1,2}(.*)","$1"+"$2")

Regex_Replace([Field],"(.*)\d{1,2}?(.*)","$1"+"$2")

Regex_Replace([Field],"(.*)(\d{1}|(\d{2})(.*)","$1"+"$3")

 

The problem:

It seems that no matter what I try, the "1" in the "15" is captured in the first (.*) expression. I'm particularly surprised that \d{1,2} doesn't work. The Regex help (RegEx Perl Syntax Guide) indicates that this structure matches "between n and m times inclusive"...

 

I'd appreciate any ideas...

Highlighted
Alteryx Certified Partner
Alteryx Certified Partner

hey @tom_montpool,

 

how about:

 

Regex_Replace([data],"(.*)\d+(_.)",'$1$2')

it works with this data....

 

Cheers,

Mark

Alteryx ACE & Top Community Contributor

Chaos reigns within. Repent, reflect and reboot. Order shall return.
Highlighted
ACE Emeritus
ACE Emeritus

When I run your version with _. instead of .*  in the last matched expression, I still get:

 

EAG_CAN_2017_Apples_A
EAG_CAN_2018_Oranges1_B

 

I'm running Alteryx Version 10.6.8.17850...

 

Highlighted
16 - Nebula
16 - Nebula

I'd go for:

REGEX_Replace([Field1],"^(.*\D)\d+(_.*)$","$1$2")

I think for @MarqueeCrew answer you need to add a ? to the .* to make it not greedy then it will work

Highlighted
Alteryx Certified Partner
Alteryx Certified Partner
Regex_Replace([data],"(.*?)\d+(_.)",'$1$2')

This works better :)

 

as @jdunkerley79 also described.  2 answers for the price of 1 

Alteryx ACE & Top Community Contributor

Chaos reigns within. Repent, reflect and reboot. Order shall return.
Labels