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

Question

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 this the right way round.

Any ideas?

Answer 1

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)