Results 1 to 6 of 6
  1. #1
    Join Date
    Jul 2010
    Posts
    7

    Question Unanswered: Access 2003: Postcode checks using Regex

    Greetings,

    I was wondering if anyone has a query to check (or as close as) for valid UK postcodes?

    I copied the regex from a blog and put it into my crieria, however it just retireved all the rows from my table when used in a query, even though I specifically entered invalid postcodes in (see below).

    Code:
    NOT like "[a-zA-Z]{1,2}[0-9][0-9A-Za-z]? [0-9][a-zA-Z]{2}"
    Valid postcodes
    A9 9AA
    A99 9AA
    AA9 9AA
    AA99 9AA
    A9A 9AA
    AA9A 9AA


    Note: It obviously isn't 100% accurate as check for ALL letters but it is a very good start

    Any ideas?

    Thanks
    Rob

  2. #2
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    LIKE does not implement regex - you will have to roll your own in VBA.
    Have a look here for better patterns:
    Postcodes in the United Kingdom - Wikipedia, the free encyclopedia
    Testimonial:
    pootle flump
    ur codings are working excelent.

  3. #3
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Some time ago, I posted a database implementing regex functions in the code bank. Here is a copy.
    Attached Files Attached Files
    Have a nice day!

  4. #4
    Join Date
    Jul 2010
    Posts
    7
    Thanks for both of the replies.

    My confusion has come about as I've used what I thought were regular expression in like statements before which function (see below)

    Code:
    like "[A-Z][0-9][0-9]"

  5. #5
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    you can use regex in VBA/VBScript if you reference the appropriate library, but AFAIK you cannot use REGEX inside JET SQL, you can in other server languages (and for all I know perhaps SQLLite and so on)
    I'd rather be riding on the Tiger 800 or the Norton

  6. #6
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by Robtyketto View Post
    Thanks for both of the replies.

    My confusion has come about as I've used what I thought were regular expression in like statements before which function (see below)

    Code:
    like "[A-Z][0-9][0-9]"
    That isn't regex, it just looks superficially like it. You could perhaps consider it a rudimentary subset implementation of regex.
    Testimonial:
    pootle flump
    ur codings are working excelent.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •