
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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)
Accepted Solutions


- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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.


- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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.

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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.


- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Rob Wunderlich wrote:The QV Cookbook has a sampled named "Regular Expression pattern matching function"
Where can I find it?


- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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.
