Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

postcode validation

i'm looking for a way of validating a postcode in load script.

i found this on the cabinet office data standards catalogue: (http://www.cabinetoffice.gov.uk/govtalk/schemasstandards/e-gif/datastandards/address/postcode.aspx)

<xsd:simpleType name="PostCodeType">
<xsd:annotation>
<xsd:documentation>complex pattern for postcode, which matches definition, accepted by some parsers is: "(GIR 0AA)|((([A-Z-[QVX]][0-9][0-9]?)|(([A-Z-[QVX]][A-Z-[IJZ]][0-9][0-9]?)|(([A-Z-[QVX]][0-9][A-HJKSTUW])|([A-Z-[QVX]][A-Z-[IJZ]][0-9][ABEHMNPRVWXY])))) [0-9][A-Z-[CIKMOV]]{2})"</xsd:documentation>
</xsd:annotation>
<xsd:restriction base="xsd:string">
<xsd:pattern value="[A-Z]{1,2}[0-9R][0-9A-Z]? [0-9][A-Z-[CIKMOV]]{2}"/>
</xsd:restriction>
</xsd:simpleType>
was just wondering if anyone had any suggestions for this?
Cheers
Rick
1 Solution

Accepted Solutions
rwunderlich
Partner Ambassador/MVP


Nick Bor wrote:There is no internal function in QV for regular expressions. You can use VBScript macros


The QV Cookbook has a sampled named "Regular Expression pattern matching function" that shows how to use RegEx to validate US Phone numbers. Similar exercise if you want to use that as a starting point.

-Rob

View solution in original post

7 Replies
Not applicable
Author

It's a regular expression for a postcode validation in UK. This one is more detailed and accurate:

GIR 0AA)|((([A-Z-[QVX]][0-9][0-9]?)|(([A-Z-[QVX]][A-Z-[IJZ]][0-9][0-9]?)|(([A-Z-[QVX]][0-9][A-HJKSTUW])|([A-Z-[QVX]][A-Z-[IJZ]][0-9][ABEHMNPRVWXY])))) [0-9][A-Z-[CIKMOV]]{2})


This one is simpler:

[A-Z]{1,2}[0-9R][0-9A-Z]? [0-9][A-Z-[CIKMOV]]{2}


There is no internal function in QV for regular expressions. You can use VBScript macros or use a database function at dataload time in QV script.

Remember in other countries there are other rules for post/zip codes. Wink

johnw
Champion III

A couple possible solutions in this thread, though it looks like the definition of a post code is a little different than I was told over there. Hopefully it can be modified to suit. The idea is brute force, just specifying the allowed characters for each position.

http://community.qlik.com/forums/t/30223.aspx


John Witherspoon wrote: I'm honestly not thinking of a better way than pure brute force:
if( index('ABCDEFGHIJKLMNOPQRSTUVWXYZ',mid(postcode,1,1))
and index('ABCDEFGHIJKLMNOPQRSTUVWXYZ',mid(postcode,2,1))
and index('0123456789' ,mid(postcode,3,1))
and index('0123456789' ,mid(postcode,4,1))
and mid(postcode,5,1) = ' '
and index('0123456789' ,mid(postcode,6,1))
and index('ABCDEFGHIJKLMNOPQRSTUVWXYZ',mid(postcode,7,1))
and index('ABCDEFGHIJKLMNOPQRSTUVWXYZ',mid(postcode,8,1)),postcode,'Error') as postcode
I mean, we could create fake functions, but I don't think it's worth the complexity. I think it would look something like this:
SET alpha = index('ABCDEFGHIJKLMNOPQRSTUVWXYZ',mid($1,$2,1));
SET digit = index('0123456789',mid($1,$2,1));
SET space = mid($1,$2,1) = ' ';
if( $(alpha(postcode,1))
and $(alpha(postcode,2))
and $(digit(postcode,3))
and $(digit(postcode,4))
and $(space(postcode,5))
and $(digit(postcode,6))
and $(alpha(postcode,7))
and $(alpha(postcode,8)),postcode,'Error') as postcode
It won't surprise me if there's a clever way to do it. I'm just not thinking of one.


Not applicable
Author

The problem is that in UK they don't have a fixed format for postcodes. Here are some examples:
RG4 5EB
W1 1EV
SW1A 1AA

It looks like car plate numbers in US. But car plate numbers in UK have more strict format.

rwunderlich
Partner Ambassador/MVP


Nick Bor wrote:There is no internal function in QV for regular expressions. You can use VBScript macros


The QV Cookbook has a sampled named "Regular Expression pattern matching function" that shows how to use RegEx to validate US Phone numbers. Similar exercise if you want to use that as a starting point.

-Rob

Not applicable
Author


Rob Wunderlich wrote:The QV Cookbook has a sampled named "Regular Expression pattern matching function"


Where can I find it?

rwunderlich
Partner Ambassador/MVP

You can download the Cookbook from

http://robwunderlich.com/Download.html

-Rob

Not applicable
Author

thanks guys used a combination of all the answers given,

used the first regex suggested but couldnt get the hang of the syntax so used the Cookbook to help me.

thanks guys.