~flupps

So, in Europe the street name comes first, then the number of the building.
In the US the number of the building comes first, then the actual street, which makes it a little bit rougher to sort.

Imagine a table like this:

mysql> SELECT * FROM numsort;

+——+
| a |
+——+
| 5 |
| a |
| 2 |
| d |
| 22 |
| c |
| 33 |
| 3 |
+——+
8 rows in set (0.00 sec)

The desired order in this case would be 2, 3, 5, 22, 33, a, b, c, d.

mysql> SELECT * FROM numsort ORDER BY a;
+——+
| a |
+——+
| 2 |
| 22 |
| 3 |
| 33 |
| 5 |
| a |
| c |
| d |
+——+
8 rows in set (0.00 sec)

Ok, we’re far from what we’re looking for here…
Let’s cast it to a numeric, even if it’s a character, each character will then be converted to a 0 and generate a warning:

mysql> SELECT * FROM numsort ORDER BY CAST(a AS UNSIGNED);
+——+
| a |
+——+
| a |
| d |
| c |
| 2 |
| 3 |
| 5 |
| 22 |
| 33 |
+——+
8 rows in set, 3 warnings (0.00 sec)

| Warning | 1292 | Truncated incorrect INTEGER value: ‘a’ |

Ok, getting closer, the numbers are ok, and the characters first, but there’s no ordering internally for the numbers, and we’re getting warnings that aren’t pretty.
Something like a IS_NUMERIC() function would be nice here…

Fortunately (?) you can take a string + 0 to get the same effect, but for some reason it doesn’t generate a warning (not sure it’s good, but for this example I guess it works…).

So, let’s put numerics first, then the characters:

mysql> SELECT a FROM numsort ORDER BY a+0 = 0, a+0;
+——+
| a |
+——+
| 2 |
| 3 |
| 5 |
| 22 |
| 33 |
| a |
| d |
| c |
+——+
8 rows in set (0.00 sec)

Our characters aren’t sorted here, though, so let’s add that to the sort list as well:

mysql> SELECT a FROM numsort ORDER BY a+0 = 0, a+0, a;

+——+
| a |
+——+
| 2 |
| 3 |
| 5 |
| 22 |
| 33 |
| a |
| c |
| d |
+——+
8 rows in set (0.00 sec)

So… we’re putting characters here as 0, what happens if something would have an address starting with a 0?

mysql> INSERT INTO numsort VALUES (0);
Query OK, 1 row affected (0.00 sec)

mysql> SELECT a FROM numsort ORDER BY a+0 = 0, a+0, a;

+——+
| a |
+——+
| 2 |
| 3 |
| 5 |
| 22 |
| 33 |
| 0 |
| a |
| c |
| d |
+——+
9 rows in set (0.00 sec)

Bah…
Ok, let’s freestyle a bit more… Let’s do a comparison, before the conversion - that’s the only way we can find if it’s 0 or a string, at least quickly…

mysql> INSERT INTO numsort VALUES (0);
Query OK, 1 row affected (0.00 sec)

mysql> SELECT a FROM numsort ORDER BY IF(a = 0, 0, 1), a+0 = 0, a+0, a;
+——+
| a |
+——+
| 0 |
| a |
| c |
| d |
| 2 |
| 3 |
| 5 |
| 22 |
| 33 |
+——+
9 rows in set (0.00 sec)

Ok, that didn’t work… why not? - AH! a=0 actually converts it to a number, since we’re doing a numeric comparison… I guess we could check if our 0 is a string or not… Since a numeric 0 will actually compare to the string ‘0′ as true (god, this is starting to get ugly…)

mysql> SELECT a FROM numsort ORDER BY IF(a = ‘0′, 0, 1), a+0 = 0, a+0, a;
+——+
| a |
+——+
| 0 |
| 2 |
| 3 |
| 5 |
| 22 |
| 33 |
| a |
| c |
| d |
+——+
9 rows in set (0.00 sec)

And to get rid of the IF() and make it shorter:

mysql> SELECT a FROM numsort ORDER BY a <> ‘0′, a+0 = 0, a+0, a;
+——+
| a |
+——+
| 0 |
| 2 |
| 3 |
| 5 |
| 22 |
| 33 |
| a |
| c |
| d |
+——+
9 rows in set (0.00 sec)

I got to admit, though…

This makes me feel dirty…