Early bird tickets for Inspire 2023 are now available! Discounted pricing closes on January 31st. Save your spot!

Alteryx Designer Discussions

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

Using RegEx to identify fields with incorrect characters

EEnglish2
6 - Meteoroid

Hello, everyone.  I'm a very new Alteryx Designer user and I'm working with my team to help standardize data formats across all of our applications.  One of the things that we need to do is to identify name fields that have non-standard characters.  For instance, a first name should contain alphabetic characters, perhaps spaces, and maybe an apostrophe.  A last name might contain a hyphen.  So, with a character set of a-z, A-Z, -, and ', we need to flag fields that do NOT match this.

 

I came up with this:

([^a-zA-Z,\-\'])

 

I think that this is the right expression, but since this is my first attempt at RegEx, I was hoping for some validation.  Do I need to include a space since that is also acceptable?

 

Thanks,

Ed

3 REPLIES 3
Felipe_Ribeir0
15 - Aurora

Hi @EEnglish2 

 

I believe that the best for this case is for you to feed the workflow with your data and see if the expression works as expected.

 

But for these default common requirements (names, zip codes, emails, etc), you can find a lot of good and complete expressions out there on the internet. For example, this one seems to cover everything that is necessary to see if a name is valid (but try with your data to see):

 

^[a-zA-Z]+(([',. -][a-zA-Z ])?[a-zA-Z]*)*$

 

Felipe_Ribeir0_0-1674675279891.png

 

 

It came from here: https://www.regextester.com/93648

 

 

EEnglish2
6 - Meteoroid

Thank you, @Felipe_Ribeir0.

 

In your example, are commas and periods considered as acceptable characters?

 

I didn't realize that RegEx could be used within a formula.  I thought the RegEx module had to be used.  That may influence our implementation.

Felipe_Ribeir0
15 - Aurora

Hi @EEnglish2 

 

Its acceptable if it is in the middle of the name. Check the last 4 rows:

 

Felipe_Ribeir0_0-1674730320212.png

Yes, there are some formulas that can help us with this. 

 

Felipe_Ribeir0_1-1674730398148.png

 

Labels