SQL Postcode exist within range

Question

I'm having the following situation:

I have a table with a list of postcodes with the format:

1234 AA (Dutch postcode)

2345 ZF

B-2345 (Belgium postcode)

B-4355

I have another table which contains postcoderanges:

PostcodeFrom

1000 AF                     
2000 ZF
B-1234

PostcodeTo

1999 ZX
2999 ZF
B-1889

I am looking for a solution how to look up the postcode value between the several ranges. First I was thinking of

SUBSTRING(MyPostcode,1,4) BETWEEN SUBSTRING(PostcodeFrom,1,4) AND SUBSTRING(PostcodeTo,1,4)

.. but then there is still the problem with the characters (not even thinking about the belgium postcodes aswell).

Could anyone help me?

Yours,

---

Thanks for your reply!

The table you drew, needs one more field: RegionCode.

RangeTable:

| RCode | PCodeFrom | PCodeTo |
| 001 | 1000 BA | 1999 ZZ |
| 002 | 1000 AA | 1999 AZ |

Notice that if a postcode is 1234 AC, it must return RegionCode: 002
To compare numbers is not hard, but how to compare characters?

I had an idea of making a table with AA - ZZ where each combination has a certain INT value, but I hope there is another, easier way.

Answer 1

You can only do this reliably (ignoring the potential un-reliability of doing this sort of range matching with postcodes) by splitting the portions of the postcode into different columns by character type.

I don't know much about Dutch postcodes, but if your formats are correct, you could create a table like:

+-------+------+
| code  | city |
+-------+------+
| 1234  | AA   |
+-------+------+

Splitting the postcodes up will allow you to do more fine-grained sorting.

Update:

Having looked at the Wikipedia page on Dutch postcodes it looks like this should work for all of them. My labels of code and city are inaccurate though.

_{Aside: I'm impressed that the Netherlands has such a sane postcode format, unlike the UK one where you need a huge regex to even decide if the format is valid.}

Update 2:

Your checking will work with characters too, but you'll be better off storing the postcodes in a separate table, with an ID. The example above was just to show splitting up the characters from the numbers, so what you'll actually want is more like:

mysql> select * from postcodes;
+------+-------+-------+
| id   | part1 | part2 |
+------+-------+-------+
|    1 |  1234 | AA    |
|    2 |  5678 | BB    |
+------+-------+-------+

When you're storing the ranges, don't store the postcodes in the ranges table, store the id for the entry in the postcodes table, like:

mysql> select * from ranges;
+-------------+---------------+-------------+
| region_code | postcode_from | postcode_to |
+-------------+---------------+-------------+
|           1 |             1 |           2 |
+-------------+---------------+-------------+

That record says "region 1 is 1234 AA to 5678 BB"

For an example, I'll say that postcodes start 0001 AA, then move to 0001 AB, all the way to 0001 ZZ, then 0002 AA and so on. This obviously isn't right but it demonstrates the theory. You need to substitute this for the algorithm you're using to define how postcodes are incremented and decremented.

When you want to find out "does postcode 3456 XY fit into region 89?", you split it into character and number, and check whether the values fit into a range. Using my algorithm, I check:

Is the number portion greater or less than the number portion of postcode_from?

If it's greater, then is it less than the number portion of postcode_to?

If you satisfy both conditions, check the letters - this is the important bit - MySQL's character set collation does allow you to say "is AB less than BC, you can have:

WHERE 'AB' < part2;

in your WHERE clause.

Using this method, you can figure out which of your regions has a start and an end that fit the value you're testing.

It's a bit long-winded but it will work without doing any conversions. You may need to check that the collation you're using fits the way the lettering sequence works for the specific type of postcode you're using though.