community
cancel
Showing results for 
Search instead for 
Did you mean: 

Alteryx designer Discussions

Find answers, ask questions, and share expertise about Alteryx Designer.
Upgrade Alteryx Designer in 10 Steps

Debating whether or not to upgrade to the latest version of Alteryx Designer?

LEARN MORE
SOLVED

Field Masking Question

Meteor

I am working on being able to test validity of data entered into a database by a user. Currently I am specifically testing Canadian Postal Code format

 

A1A 1A1 (where A is a letter A to Z and 1 is a number 1 through 9)

 

I am wondering if there is an efficient way to test for this to ensure the format has been entered correctly?

Stealing entirely from (https://www.oreilly.com/library/view/regular-expressions-cookbook/9781449327453/ch04s15.html):

 

REGEX_Match([Input],'^(?!.*[DFIOQU])[A-VXY][0-9][A-Z] ?[0-9][A-Z][0-9]$')

should validate all Canadian Post Codes

 

Put this in a filter tool and the T output will be the valid ones

Alteryx Certified Partner
Alteryx Certified Partner

@jnans,

 

I like the resourceful @jdunkerley79  solution.  Here however is a response  that makes 0 use of RegEx.  By the way, if I wanted to implement your requirements the way that you wrote them, here is an easy to read formula:

Regex_Match([Postal Code],"[A-Z][1-9][A-Z]\s[1-9][A-Z][1-9]")
// the \s is a space ....

Using only string functions, here's a nifty solve:

ToNumber(Substring([Postal Code],0,1))=0
AND
ToNumber(Substring([Postal Code],1,1))>0
AND
ToNumber(Substring([Postal Code],2,1))=0
AND
Substring([Postal Code],3,1)= " "
AND
ToNumber(Substring([Postal Code],4,1))>0
AND
ToNumber(Substring([Postal Code],5,1))=0
AND
ToNumber(Substring([Postal Code],6,1))>0

Cheers,

 

Mark

Alteryx ACE & Top Community Contributor

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

Hi @MarqueeCrew , @jdunkerley79

 

In the postal code, there will be possible of having '0' zero in the numerical section eg:  X0E 0P0, K1M 0T3. So you have to consider 0 in your condition.

 

ToNumber(Substring([Postal Code],0,1))=0
AND
ToNumber(Substring([Postal Code],1,1))>=0
AND
ToNumber(Substring([Postal Code],2,1))=0
AND
Substring([Postal Code],3,1)= " "
AND
ToNumber(Substring([Postal Code],4,1))>=0
AND
ToNumber(Substring([Postal Code],5,1))=0
AND
ToNumber(Substring([Postal Code],6,1))>=0

 

or

 

Regex_Match([Postal Code],"[A-Z][0-9][A-Z]\s[0-9][A-Z][0-9]")

 

 

Alteryx Certified Partner
Alteryx Certified Partner

@jnans ,

 

See how easy it was to modify my RegEx!  @Kaviyarasan_P did it with ease.  But as I'm from down South, I'm not so Canadian Savvy. 

 

Cheers,

 

Mark

Alteryx ACE & Top Community Contributor

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

Thank you all for the help with this. I knew there had to be an easy way of doing it. I had caught the 0. Thank you everyone for the support and assistance.

Highlighted
Magnetar

Hi @jnans 

 

If you're really trying to validate Canadian postal codes as opposed to an alternating character/digit string, then @jdunkerley79 is the only person to have the correct answer. See this page (old but still valid.)

 

The Canadian postal code is designed to be mechanically parsed(OCR) even if hand written.    The Letters DFIOQU are explicitly forbidden for disambiguation purposes anywhere in the string(Is it a letter "O" or a zero, etc) and the first character, which represents the main geographical region, cannot be W or Z.  

 

 

Meteor

Thank you. I have a few different tests being run on the postal codes itself and so the alternating with a space at least was able to get that portion of it. I will work more with the first formula a bit later as I could not get it to work at all. It had produced all false results for me which I knew to not be the expected case based on the data I was using

Labels