MySQL match first part of a postcode

Databases
1

Hi Guys!

I’m trying to match the first part of a postcode. For example when someone types CM129PP or CM12 9PP it should match the row in the database that is “CM12”.

Here’s my SQL that I’m using that the moment, but it doesn’t work.

select * from uk_postcode_towns where postcode like 'cm12 9pp%'

Any ideas?

2

Yes, my idea is ‘cm12 9pp%’ won’t match “CM12” because ‘cm12 9pp%’ means that it’ll match all rows with values that start with ‘cm12 9pp’. And clearly “CM12” is not such a value.

If you want to match only a part of the given value, then you’ll have to do the confrontation with only that part:


select * 
from uk_postcode_towns 
where postcode like 'cm12%'

If you’re using PHP, use substr() to get the part you want to use in your query.

3

AFAIK with any UK postcode the last part is always NLL and never NNLL (where N is a digit (0-9) and L is a letter) so in whatever the server-side language is you could if the user hasn’t put in a space, split the string, counting.

<?php

// Postcode Splitting Test

$postcode = 'AB12 3CD';

$pc_length = strlen($postcode);

$pc1_len = $postcode - 3;

$pc['area']     = rtrim(substr($postcode,0,$pc1_length));
$pc['sub_area'] = substr($postcode,-3);

?>

That’s a way that works, there may well be a better method out there but it gets you the two parts of the post code each as a seperate part of the $pc array, whether there is a space in-between or not.