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

Field Masking Question

jnans
8 - Asteroid

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?

7 REPLIES 7
jdunkerley79
ACE Emeritus
ACE Emeritus

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

MarqueeCrew
20 - Arcturus
20 - Arcturus

@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 restart. Order shall return.
Please Subscribe to my youTube channel.
Kaviyarasan_P
8 - Asteroid

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]")

 

 

MarqueeCrew
20 - Arcturus
20 - Arcturus

@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 restart. Order shall return.
Please Subscribe to my youTube channel.
jnans
8 - Asteroid

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.

danilang
19 - Altair
19 - Altair

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.  

 

 

jnans
8 - Asteroid

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