Field Masking Question

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?

Datasets

Reporting

Regex

Tips and Tricks

Parse

Optimization

Expression

Data Investigation

Accepted answers

jdunkerley79

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

@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

All comments

jdunkerley79

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

@jnans,

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

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

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.

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15866

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703