phX phX - 3 years ago 78
SQL Question

Sorting a column with numbers, strings and nils

I have a column that includes numbers (as strings), strings and nils.
I would like to order all numbers by numerical values, then strings alphabetically, then all nils and empty strings sorted through another column.

so, given:

+----+-----+------+
| id | val | name |
+----+-----+------+
| 0 |bbb | |
| 1 |aaa | |
| 2 |0920 | |
| 3 |320 | |
| 4 | |c |
| 5 |NULL |b |
| 6 | |a |
+----+-----+------+


I want:

+----+-----+------+
| id | val | name |
+----+-----+------+
| 3 |320 | |
| 2 |0920 | |
| 1 |aaa | |
| 0 |bbb | |
| 6 | |a |
| 5 |NULL |b |
| 4 | |c |
+----+-----+------+


thanks!

Answer Source

SqlFiddleDemo

First use a CASE to create groups, numbers go first, null go last, the rest in on the middle

Then sort by the numeric value, and the name

SELECT *
FROM Table1
ORDER BY CASE WHEN `val` REGEXP '[0-9]+' THEN 1 
              WHEN `val` IS NULL THEN 3
              ELSE 2
         END,
         CASE WHEN `val` REGEXP '[0-9]+' THEN CONVERT(`val`, SIGNED INTEGER)
              ELSE 0
         END,
         name

OUTPUT

| id |    val |   name |
|----|--------|--------|
|  3 |    320 | (null) |
|  2 |   0920 | (null) |
|  0 |    bbb | (null) |
|  1 |    aaa | (null) |
|  6 | (null) |      a |
|  5 | (null) |      b |
|  4 | (null) |      c |
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download