1

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.

Cœur
  • 37,241
  • 25
  • 195
  • 267
Chris Marshall
  • 11
  • 1
  • Show us what you've tried so far and describe in what way it is not doing what you want. – The Archetypal Paul Oct 15 '10 at 10:50
  • why not only use the first to characters of the user input for matching? are all starting letters of length 2? in pure SQL: `where post_code = SUBSTRING('LS19 1AB',0,2)` – sfussenegger Oct 15 '10 at 11:02
  • yeh unfortunately some areas such as liverpool start with one letter l - however it then follows with a number so if i could extract all numbers this would work – Chris Marshall Oct 15 '10 at 12:13

1 Answers1

2

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)
nos
  • 223,662
  • 58
  • 417
  • 506
  • ahh - wonderful, however if I have another row with value 'L' it will also return this, any way to match the first letter or two letters before the numbers – Chris Marshall Oct 15 '10 at 12:28
  • 1
    Sounds a bit harder, if you want the longest match, and only one match, you could add a `order by post_code desc limit 1;` though, on the other hand, if you know beforehand to only match 1 or 2 characters, you'd just do ` where SUBSTRING('LS19 1AB',1,2) = post_code;` when matching 2 letters. – nos Oct 15 '10 at 14:23