trim long postcode to retrieve matching short postcode record

[axLW]

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?

 

[guitarzan]

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.

 

[axLW]

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

eg, they enter:

SW1A1AA which is still valid...

 

[guitarzan]

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

http://www.planet-source-code.com/vb/scripts/ShowCode.asp?txtCodeId=7843&lngWId=4

 

[ChrisHirst]

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

 

[xwb]

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

 

[axLW]

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.

 

[axLW]

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.

 

[guitarzan]

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)

 

[xwb]

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

 

[axLW]

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.

 

[axLW]

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?

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

 

[axLW]

Please help me someone!

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

Link

 

[axLW]

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

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

 

[guitarzan]

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.

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

Function IsLetter(strValue [s]As String[/s]) [s]As Boolean[/s]
    Dim intPos [s]As Integer[/s]
    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

 

[axLW]

Ok let me try and make sense of that...

 

[axLW]

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

<%

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:

Microsoft VBScript compilation error '800a0400'

Expected statement

/02.asp, line 16

Case 65 [b]T[/b]o 90, 97 To 122
--------^

 

[guitarzan]

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

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

 

[axLW]

IT WORKS!