Regex Tool Executing Differently Than Online

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/

Regex

Accepted answers

clmc9601

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

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?

regexlookahead.yxmd

All comments

OllieClarke

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

ZMcCain

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.

RegexTool1.PNG

RegexTool2.PNG

clmc9601

Hi @ZMcCain,

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.

BretCarr

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.

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15866

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703