Matching long string (full postcode) agains short string (start of postcode) in mysql

I have a table that contains a list of the starting letters in a post code e.g. LS for Leeds and SO for Southampton.

I want to match these against a user entered full postcode e.g. LS19 1AB.

I've looked into using LIKE and some regexp stuff in my query but I'm struggling to find a way to do thi开发者_运维百科s the right way round.

You can turn the where clause around, and do some string manipulation tricks:

create table post_codes(
  post_code varchar(32)
);


mysql> insert into post_codes values("AS");
Query OK, 1 row affected (0.00 sec)

mysql> insert into post_codes values("LS");
Query OK, 1 row affected (0.00 sec)

mysql> select post_code from post_codes where 'LS19 1AB' like CONCAT(post_code,'%');
+-----------+
| post_code |
+-----------+
| LS        |
+-----------+
1 row in set (0.00 sec)