Field Masking Question

[jnans]

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?

[jdunkerley79 · Accepted solution]

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 · Accepted solution]

@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

[Kaviyarasan_P]

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]

@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

[jnans]

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]

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]

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