INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

trim long postcode to retrieve matching short postcode record

trim long postcode to retrieve matching short postcode record

(OP)
I have a database that contains UK postcodes and prices. Each postcode record has a matching price (see below):

W1 £45.00
W2 £48.00
W3 £54.00
SW5 £48.00
SW1 £34.00
SW11 £45.00

You may or may not know, but UK postcodes are alphanumeric and between six and eight characters long.

On my booking form I am taking a full postcode, eg: SW1A 1AA

In my database I am only storing a single value (£34.00) for SW1. This should be a matching record for ANY SW1 full postcode.

I thought about matching the first three characters of the postcode, but that brings up another issue.

If somebody enters a full SW11 postcode, the first three characters will match both SW1 and SW11 so that won't work.

This is why I think I need something that does the following:

Do the first four characters match?
if YES - retrieve record
if NO - Do the first three characters match?
if YES - retrieve record
if NO - Do the first two characters match?
if YES - retrieve record
if NO - Postcode is invalid

Can anybody suggest a better way of doing this? If you think that this method would work, how would I go about writing this function?

RE: trim long postcode to retrieve matching short postcode record

If sounds like you could just truncate everything after the first "word". The Split command will do this nicely. You could also InStr, and Mid, Left, Right etc.

RE: trim long postcode to retrieve matching short postcode record

(OP)
What happens if the customer does not include a space in their full postcode?

eg, they enter:

SW1A1AA which is still valid...

RE: trim long postcode to retrieve matching short postcode record

I know nothing about UK postcodes, so I can't give you advice on the most efficient way to extract the part of the code you want (the outward code I guess?). Wikipedia shows that the possible formats are below (where A is a letter and 9 is a digit):
AA9A 9AA
A9A 9AA
A9 9AA
A99 9AA
AA9 9AA
AA99 9AA

So a valid full postcode, if you get rid of the spaces, should always end with a number and two letters (If I'm understanding that correctly??) If that assumption is correct, get rid of the spaces, make sure the length is at least 5 characters, make she the last 3 characters are a number followed by two letters, and if so, truncate the last 3 characters and you are left with the outward code.

But, there is code out there that will validate UK postcodes properly, you may need to modify them to do what you want.

FAQ707-6344: Validate a UK Postcode
http://www.planet-source-code.com/vb/scripts/ShowC...

RE: trim long postcode to retrieve matching short postcode record

CODE --> VBScript

if instrrev(shortstr, longstr) > 0 then 
     'match found
else
     'no match
end if 


Chris.

Indifference will be the downfall of mankind, but who cares?
Time flies like an arrow, however, fruit flies like a banana.
Webmaster Forum

RE: trim long postcode to retrieve matching short postcode record

If it is a long postcode, just remove the last 3 characters. The sector code only goes from 0-9.

https://www.mrs.org.uk/pdf/postcodeformat.pdf

RE: trim long postcode to retrieve matching short postcode record

(OP)
If it is a long postcode, just remove the last 3 characters. The sector code only goes from 0-9

This would work but then I'd need to add instances in my database of all postcodes that have letters.

So for SW1, I would need to store:

SW1A
SW1E
SW1H
SW1P
SW1V
SW1W
SW1X
SW1Y

To do this for every single postcode is going to be A LOT of work.

RE: trim long postcode to retrieve matching short postcode record

(OP)
What I should explain as well is that I do not need this function to validate a postcode. My 'address finder' will not give the user an address if the postcode they enter is not valid. The postcode will ALWAYS be valid, but it may or may not contain a space and my only requirement is to link that valid postcode to my shortened postcodes in my database.

RE: trim long postcode to retrieve matching short postcode record

First, remove all spaces.
Then, if the 4th character is a letter, search your database for the first 3 characters (SW1)
Or if the 4th character is a number, search for you the first 4 characters (SW11)

RE: trim long postcode to retrieve matching short postcode record

Alternatively

1) remove all spaces
2) if it is 5 or more characters, remove the last 3
3) you will then be left with 3 or 4 character postcode areas and district
4) If W1A to W1W all have the same price but W11 has a different price then you could remove any trailing letters.

So

M1 1AA = M1
M60 1NW = M60
CR2 6XH = CR2
DN55 1PT = DN55
W1A 1HQ = W1
EC1A 1BB = EC1

RE: trim long postcode to retrieve matching short postcode record

(OP)

Quote (xwb)

1) remove all spaces
2) if it is 5 or more characters, remove the last 3
3) you will then be left with 3 or 4 character postcode areas and district
4) If W1A to W1W all have the same price but W11 has a different price then you could remove any trailing letters.

I like the sound of this. Let me give it a go.

RE: trim long postcode to retrieve matching short postcode record

(OP)
Ok. I'm getting somewhere. I am able to

1) remove all spaces
2) if it is 5 or more characters, remove the last 3

The only part I'm struggling with is to remove any trailing letters (or if trailing character is a letter, remove it)

Can somebody help me out with the last part of this code?

CODE

postcode = Replace(postcode," ","") 
If Len(postcode) >5 Then
postcode = LEFT(postcode, (LEN(postcode)-3))
***missing line***
End If 

RE: trim long postcode to retrieve matching short postcode record

(OP)
Please help me someone!

I found this link that says it is not straightforward...

Link

RE: trim long postcode to retrieve matching short postcode record

(OP)
This doesn't work... am I along the right lines?

CODE

If Right(pickup,1,[a-z],true) Then
pickup = LEFT(pickup, (LEN(pickup)-1))
End If 

RE: trim long postcode to retrieve matching short postcode record

No, you should check the syntax of Right, Left, and Mid (I hinted at this earlier).

Why not just use the IsLetter function in your link? Granted it's VBA so there are no type declarations but otherwise it should work for you.

CODE

If IsLetter(Right(pickup,1) Then
   ...
End If 

Function IsLetter(strValue As String) As Boolean
    Dim intPos As Integer
    For intPos = 1 To Len(strValue)
        Select Case Asc(Mid(strValue, intPos, 1))
            Case 65 To 90, 97 To 122
                IsLetter = True
            Case Else
                IsLetter = False
                Exit For
        End Select
    Next
End Function 

RE: trim long postcode to retrieve matching short postcode record

(OP)
Ok let me try and make sense of that...

RE: trim long postcode to retrieve matching short postcode record

(OP)
If you could just tweak this for me I'd be grateful. I honestly have tried to get it working...

CODE

<%

Function IsLetter(strValue)
    Dim intPos
    For intPos = 1 To Len(strValue)
        Select Case Asc(Mid(strValue, intPos, 1))
            Case 65 To 90, 97 To 122
                IsLetter = True
            Case Else
                IsLetter = False
                Exit For
        End Select
    Next
End Function 

sTempVar = "&#"

pickup = request.form("frmpick")
dropoff = request.form("frmdrop")

pickup = Replace(pickup," ","") 

If Len(pickup) >5 Then
pickup = LEFT(pickup, (LEN(pickup)-3))
End If

If IsLetter(Right(pickup,1)) Then
pickup = LEFT(pickup, (LEN(pickup)-1))
End If

%>

<p><%=pickup%></p> 

The code above is giving me the following error:

CODE

Microsoft VBScript compilation error '800a0400'

Expected statement

/02.asp, line 16

Case 65 To 90, 97 To 122
--------^ 

RE: trim long postcode to retrieve matching short postcode record

Ahh, I see the problem. That function wasn't as useful as I thought. Try this instead:

CODE

Function IsLetter(strValue)
   Dim x, a
   IsLetter = False
      For x = 1 To Len(strValue)
         a = Asc(UCase(Mid(strValue, x, 1)))
         If a >= 65 And a <= 90 Then
            IsLetter = True
         Else
            IsLetter = False
            Exit Function
         End If
   Next
End Function 

RE: trim long postcode to retrieve matching short postcode record

(OP)
IT WORKS!

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close