Page 1 of 1

Give output if between postcodes

Posted: Fri Nov 12, 2010 11:13 am
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)

Re: Give output if between postcodes

Posted: Mon Nov 15, 2010 2:36 pm
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.

Re: Give output if between postcodes

Posted: Thu Nov 18, 2010 7:41 pm
by josh
Write a simple hash function to generate a numeric token. Incorporate into the hash routine any rules about sort order.