Give output if between postcodes

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
MrConn
Forum Newbie
Posts: 1
Joined: Thu Nov 11, 2010 4:29 pm

Give output if between postcodes

Post by MrConn »

Hi,
Im struggeling with a query:
I want to have a IF BETWEEN statement between two fields: postcode_min and postcode_max

But as you might know, not all postcodes in the world are just numeric...

So: How can I get a IF BETWEEN statement when the fields might be something like this: postcode_min = E3 2AA and postcode_max = E3 2BB (UK postcodes)
User avatar
tr0gd0rr
Forum Contributor
Posts: 305
Joined: Thu May 11, 2006 8:58 pm
Location: Utah, USA

Re: Give output if between postcodes

Post by tr0gd0rr »

I must say the problem seems academic! What in the world would it tell you if a post code was between two others?!

Anyway, at first glance, it seems like it works fine with letters:

Code: Select all

SELECT 'B' BETWEEN 'A' AND 'C'; -- 1
SELECT 'D' BETWEEN 'A' AND 'C'; -- 0
SELECT 'b' BETWEEN 'A' AND 'C'; -- 1
SELECT 'A5' BETWEEN 'A1' AND 'C8'; -- 1
The problem is that numbers come before letters in ascii so 1 < A but you probably want it to go from A-Z followed by 0-9. The following illustrates the problem.

Code: Select all

SELECT 'E3 2AC' BETWEEN 'E3 2AA' AND 'E3 2BB'; -- 1
SELECT 'E3 2A5' BETWEEN 'E3 2AA' AND 'E3 2BB'; -- 0
(I'm using MySQL 5.1.33 with collation utf8_general_ci)

Tricky; maybe remap to ascii where 0-9=A-J and A-P=K-Z and Q-Z=0-9? That would cause 1 > A.
josh
DevNet Master
Posts: 4872
Joined: Wed Feb 11, 2004 3:23 pm
Location: Palm beach, Florida

Re: Give output if between postcodes

Post by josh »

Write a simple hash function to generate a numeric token. Incorporate into the hash routine any rules about sort order.
Post Reply