Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Alteryx Designer Desktop Discussions

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

Regex Tool Executing Differently Than Online

ZMcCain
6 - Meteoroid

I have a problem that involves a string of fields, separated by commas. One of these fields begins and ends with a comma, which causes problems when the string is parsed. The solution I am currently looking at is using the Regex tool to find this field, which always ends up being 7 symbols long, always starts and ends with a comma, and always contains at least one letter and one number (example: ,sf4g7rd,). There are other fields that contain strings that are 7 characters long, but they do not contain numbers, and we do not want these to match - therefore, the expression must check this. After looking online at multiple regex test sites (listed below), the expression ,(?=.+[0-9])(?=.+[a-zA-Z])[\w]{7}, was found to work - it would match test cases: ,sd4fs8d, while not matching test cases: ,tdvanva, or ,ALBANYX, which is exactly what I need. However, when this same expression is used in the Regex tool on Alteryx, all 7-length strings were matched, including those with and without numbers. Why is this? Is there an expression that will get what I need done?

 

Regex Test sites: 

https://regexr.com/

https://regex101.com/

 

8 REPLIES 8
OllieClarke
15 - Aurora
15 - Aurora

Hi @ZMcCain, can you share your workflow, or some data?

If I put your 3 test cases and your RegEx into my Alteryx regex tool it seems to work fine

OllieClarke_0-1613067050109.png

 

ZMcCain
6 - Meteoroid

What I can say is that it is one solid string of fields, separated by commas, for example: 

 

Planogram,024ftX078in SD A ALBANYX R,222815,288,78,24,-1,0,1,288,5.75,24,1,12632256,1,7.81,1,0,65535,1,0,0,0,0,,,0,0,0,0,3,1,1,0,1,0,0,0,0,2,3,1,0,1,0,0,0,0,3,3,1,1,1,0,1,1.25,92,0007,4/6/2020 12:00:00 AM,01 02 03 04 05 06 07 08 09 10 11 12 13 14 15 16,

 

and, contained in this data are strings like the cases mentioned, i.e. 

 

Planogram,024ftX078in SD A ALBANYX R,222815,288,78,24,-1,0,1,288,5.75,24,1,12632256,1,7.81,1,0,65535,1,0,0,0,0,,,0,0,0,0,3,1,1,0,1,0,0,0,0,2,tdvanva,3,1,0,1,0,0,0,0,3,3,1,1,,sdf4f8d,,1,0,1,1.25,92,0007,4/6/2020 12:00:00 AM,01 02 03 04 05 06 07 08 09 10 11 12 13 14 15 16

 

When this is run through the Regex tool, both are replaced.

In the two attached pictures, the first one is the Regex tool itself. The second picture is after the tool is ran and the data has been parsed - the first highlighted "Replaced" is where tdvanva was, and the second highlighted "Replaced" is where ,sdf4f8d, was.

clmc9601
13 - Pulsar
13 - Pulsar

Hi @ZMcCain,

 

To my understanding, there are several different versions of regex syntax-- dialects, you could say. Alteryx uses Perl. Here is a thorough post regarding Alteryx's guide to Perl. I use rubular.com (which uses Ruby regex) to test my regex expressions. It's close enough to Perl to work for most things. However, other dialects are not as compatible with Perl and might function differently in an online forum than within Alteryx. 

 

Does this regex expression help?

,(?=\w*\d)\w{7},

 

It looks for a string that has at least one digit and possible letters out front, but it doesn't include them in the count of seven characters.

 

OllieClarke
15 - Aurora
15 - Aurora

Hey @ZMcCain,

I tweaked the regex to this:

,(?=[^,]*\d)(?=[^,]*[a-zA-Z])\w{7},

as the old regex was incorrectly tagging 'tdvanva' as it had numbers after it. So I made sure the lookaheads can't include commas. Does that help?

OllieClarke_0-1613129110828.png

 

 

OllieClarke_1-1613129164644.png

 

 

BretCarr
10 - Fireball

I’m on a mobile phone right now but I think this may be the pattern that will give you your desired results too:

 

(?:[a-zA-Z]{7}|([a-zA-Z0-9]{7}))

**UPDATE** This works in a test environment but not in Alteryx.

ZMcCain
6 - Meteoroid

Thank you for the help, this one works perfectly!

BretCarr
10 - Fireball

@ZMcCain : did you happen to try mine? I’m just curious if it worked too.

 

thanks!

ZMcCain
6 - Meteoroid

Unfortunately, yours did not! Thank you for replying though!

Labels