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)
Give output if between postcodes
Moderator: General Moderators
Re: Give output if between postcodes
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:
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.
(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.
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'; -- 1Code: Select all
SELECT 'E3 2AC' BETWEEN 'E3 2AA' AND 'E3 2BB'; -- 1
SELECT 'E3 2A5' BETWEEN 'E3 2AA' AND 'E3 2BB'; -- 0Tricky; 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
Write a simple hash function to generate a numeric token. Incorporate into the hash routine any rules about sort order.