cancel
Showing results for
Did you mean:

# Alteryx designer Discussions

Find answers, ask questions, and share expertise about Alteryx Designer.
Announcement | We'll be doing maintenance between 9am-11am MT on Saturday, January 25th, which may impact your experience. Thanks for your patience as we work on improving the community!
SOLVED

## Field Masking Question

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?

Nebula

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

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.
Highlighted
Asteroid

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

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.
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.

Nebula

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.

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