mySQL - selecting part of a column.

jgh84

In the UK postcodes are in the one of the following two formats: (where L = alphabetic letter, and N = integer).

LN NLL ; or LLNN NLL

I would like to select all people in the same postcode zone. (i.e. L? ???, or LL?? ???).

At first this query looked like a good start:
"select * from memberpostcodes where postcode like 'N%'";

The problem with that search is, that it'll pick up postcodes that start with the same letter, but also have an additional letter.

Is there any way, in SQL, to say - "select where the first character is a letter that matches, and the second character is a number" if needs be?

Thanks

liquorvicar

regular expressions:

SELECT * FROM memberpostcodes WHERE postcode REGEXP '^N[0-9]'

see MySQL REG EX docs

jgh84

Cheers - appreciated! I didn't know you could use regexp in sql calls too, cool!!

liquorvicar

btw, don't forget BFPO postcodes and other such oddities.

jgh84

Only got postzon data for inland addresses.

Interesting problem - I've just had a google, and can't seem to find any mention of my this problem, so if someone could test this on their PHP that would be grand:

If I make a plain file, and just state that:

$sql = "SELECT * FROM memberpostcodes WHERE postcode REGEXP '^{$input_pcode[0-9]'} LIMIT $start,10";

I get a problem - the page won't run, just a white page is returned. PHP borks.

If I state instead that:

$sql = "SELECT * FROM memberpostcodes WHERE postcode REGEXP '^{$input_pcode'} LIMIT $start,10";

The script runs fine.

It seems that PHP doesnt like the square brackets being in the string for some reason.

Is this normal?

liquorvicar

PHP would have a problem with that as square brackets are used for arrays. it thinks $input_pcode[0-9] is an array call.
try

$sql = "SELECT * FROM memberpostcodes WHERE postcode REGEXP '^{$input_pcode}[0-9]' LIMIT $start,10";

$sql = "SELECT * FROM memberpostcodes WHERE postcode REGEXP '^".$input_pcode."[0-9]' LIMIT $start,10";