Email validation REGEX - Alteryx returning error - HELP

[GavinAttard]

Hi Folks

I am using the following regex to validate email addresses: ([\w-\.]+)@((?:[\w]+\.)+)([a-zA-Z]{2,4})

IT works fine in a regex tester, but when i put it in Alteryx using the Regex_Match in a filter it returns the following error: 

Warning: Filter (16): REGEX_MATCH: RegEx: A character range was invalid, for example [d-a] at character 5

Any help trying to debug this would be greatly appreciated. 

cheers

[mborriero]

Why don't you use the Cleanse Macro?

[mborriero]

If you want to use regex, you can try o use this formula:

([\w\-\.]+)@((?:[\w]+\.)+)([a-zA-Z]{2,4})

your problem was probably due to the "-" char.

[GavinAttard · Accepted solution]

Ahhhh... The 5 minutes after you post a help request, then solve the problem... 

ok so i isolated the problem to the the first capture group

([\w-\.]+)

Alteryx didn't like the \. - even though it is technically correct

The fix: Swap positions - ([\.\w-]+)

and it liked that....

so the full regex is now: ([\.\w-]+)@((?:[\w-]+\.)+)([a-zA-Z]{2,4})

ps, i also improved this to catch possible domains separated by '-'

Cheers 

[GavinAttard]

Interesting looking macro.. don't seem to have that one... 

is that standard issue?

[mborriero]

I believe it is a standard macro from version 10.6 or at least 11.0

If not, you can download it here:

https://community.alteryx.com/t5/Engine-Works-Blog/The-Cleanse-Macro/ba-p/2363

[simonaubert_bd]

I tried this one and it works fine

 REGEX_MATCH([Email],"(?:[a-z0-9!#$%&'*+/=?^_`{|}~-]+(?:\.[a-z0-9!#$%&'*+/=?^_`{|}~-]+)*|"+"'"+"(?:[\x01-\x08\x0b\x0c\x0e-\x1f\x21\x23-\x5b\x5d-\x7f]|\\[\x01-\x09\x0b\x0c\x0e-\x7f])*"+"'"+")@(?:(?:[a-z0-9](?:[a-z0-9-]*[a-z0-9])?\.)+[a-z0-9](?:[a-z0-9-]*[a-z0-9])?|\[(?:(?:25[0-5]|2[0-4][0-9]|[01]?[0-9][0-9]?)\.){3}(?:25[0-5]|2[0-4][0-9]|[01]?[0-9][0-9]?|[a-z0-9-]*[a-z0-9]:(?:[\x01-\x08\x0b\x0c\x0e-\x1f\x21-\x5a\x53-\x7f]|\\[\x01-\x09\x0b\x0c\x0e-\x7f])+)\])")

According to https://emailregex.com/, it's the RFC 5322 Officia Standard.

[JophyJoy]

How do you incorporate the changing special character acceptance in the local name across different domains? For example Gmail don't allow any special characters while hotmail can take - _ +. Seems bit complicated? Any easy way to solve this?

[simonaubert_bd]

Hello @JophyJoy 

I would say : RFC 5322 Official Standard and nothing else.