How to ignore second letter in first part of UK postcodes?

[iainlang]

I've inherited a php/mysql app which sorts and displays UK postcodes.

When sorting, it uses

SELECT... WHERE postcode like "$postcode%"

and for a $postcode of, say E, this works Ok except it brings to screen everything starting EH (for Edinburgh) *and* everything starting E (for East London).  I need to refine this further.  How do I bring to screen *only* those starting, say, E (for East London), S for Sheffield and G for Glasgow, etc?

I think I need to be able to ignore any postcode whose second letter is *not* a letter but is a number or space, or blank, but I don't know how to do this.

 

Any help gratefully received.

[jumpenjuhosaphat]

I did something similar with UK postcodes.  Do you have a database with all of the UK postcodes?  If not, I'd be glad to share mine, I have a DB that is a list of all UK postcodes that I use to determine distance between postcodes...Maybe that would help.  Instead of sorting WHERE and LIKE, you could do it by distance between postcodes?  I'll throw the PHP script in that I use to calculate distance too if you are interested......

[iainlang]

The data is supplied by the people living at each address, so, thank you, but I am not trying to determine which address lies within which postcode and set the postcode accordingly. I have the information, I am looking on how to process it on the way out of the database.

[MadTechie]

try

SELECT... WHERE postcode REGEXP "^$postcode"

 

basically will find PostCodes that start with $postcode.

 

or

 

I think I need to be able to ignore any postcode whose second letter is *not* a letter but is a number or space, or blank, but I don't know how to do this.

 

this should do the above

SELECT... WHERE postcode REGEXP '^w+[\s0-9]+'

 

 

Either way i hope that helps

[neel_basu]

I think the method of MadTechie will work.

Can you post a sample Post code of UK ??

[iainlang]

Thank you but the normal full postcode SELECT works with (minus the cosmetics) -

 

$Query="SELECT distinct email FROM $TableName WHERE postcode like \"$postcode%\" ";

 

For the first-letter-only operation, the code for the input page is -

 

Choose the appropriate City - Birmingham <input type="radio" name="postcode" value="B"> Glasgow <input type="radio" name="postcode" value="G"> Liverpool<input type="radio" name="postcode" value="L"> Manchester <input type="radio" name="postcode" value="M"> Sheffield <input type="radio" name="postcode" value="S"> <input type="submit" name="submit" value="Search">

 

and the first-letter-only handling page that gets passed to is -

 

$Query="SELECT distinct email FROM $TableName WHERE postcode REGEXP '^w+[\s0-9]+' ";

 

which doesn't work.  Boo-hoo.

 

Can anyone see what I'm doing wrong?

[MadTechie]

The Example i posted should work but it depends on the format you accept

 

UK Postcodes are as follows

 

NE7 1QA

SW4 0LH

etc

 

IF you don't have a space then remove the \s

if you only want to search for the first letter then use '^\w'

 

hope this helps

 

 

 

NOTE: the REGEXP should be '^\w+[\s0-9]+'

[neel_basu]

Ok these are the sample Postcodes

NE7 1QA

SW4 0LH

I understood only this part properly

I think I need to be able to ignore any postcode whose second letter is *not* a letter but is a number or space, or blank, but I don't know how to do this.

That means

I think I need to be able to accept any post code whose second letter is a letter(Character) *not* a number or spaces.

So here is the regex for that.

\w(?:[a-zA-Z])

I didn't understand

IF you don't have a space then remove the \s

if you only want to search for the first letter then use '^\w'

[iainlang]

Thanks to everyone for helping in this matter.  Alas, we're still not there.

 

I think the best thing I can do is offer examples.  Here goes -

 

There are five cities in the UK which have a single-letter, and a single letter only, as the first part of their postcodes -

 

Glasgow    G

Birmingham  B

Liverpool    L

Manchester    M   

Sheffield    S

 

I'm going to stick with Sheffield and the London SW postcode for examples.

 

Computers don't sort UK postcodes naively correctly for our purposes, and so -

 

sw1 1aa

sw10 1aa

sw2 1aa

sw20 1aa

 

is not the sequence I need.

 

However -

 

sw 1 1aa            (note the [space] after the "sw")

sw 2 1aa            (note the [space] after the "sw")

sw10 1aa

sw20 1aa

 

come out in the order I need, and so that is how the postcodes are stored in the database (they are processed on the way in to produce that format, specifically so they can be sorted in the required order).

 

The same applies to  -

 

s1aa

s41 1aa

s2 1aa

s22 1aa

 

and  -

 

s 1 1aa            (note the [space] after the "s")

s 2 1aa            (note the [space] after the "s")

s22 1aa

s41 1aa

 

The first $Query works great in that -

SELECT * FROM database WHERE postcode LIKE "postcode%"

when passed "sw" (i.e., two characters), returns  -

 

sw1 / sw10 / sw999 / etc and that is ideal.

 

Unfortunately, when it is passed "s" (i.e., one character), it returns  -

 

sw1 /sw10/ sw999 etc    AND      s1 / s10 / s999

 

I'm hoping for code for a separate $Query which will, when passed a one-letter input, like "s" will ignore all the "sw..." variations and return only -

 

s 1 1aa

s41 1aa

s 2 1aa

s22 1aa

 

whether or not the second character is a number or a space or even a NULL (I'm dealing with people with learning difficulties who can sometimes not recall even anything beyond the "s"; there is also the occasional typo).

 

So the $Query has to return matches which use only the initial character, which must be a letter, and has to ignore anything which has a letter as the second character  Like -

 

sw[anything]

 

(I'm hopeless at explaining things....)

 

Can I do this with php in any way?

 

 

[iainlang]

Ok, if we look at it another way, can someone guide me as to how I can check with php as to whether any character in a string is -

ascii, or not, and

letter, or number.

 

TIA.