Sorting field by numerical value and lexicographical value

Question

I have a table of orders. For each order, we allow the user to enter their (non-unique) order number. This can we whatever they want.

I have these orders displayed in an HTML table, with the ability to sort the orders by various fields, such as order number.

One of our clients noticed an issue with the sorting. Since the order numbers are stored as VARCHAR, they are sorted lexicographically. Problem is, not all order numbers are numerc, some are words, and others are alphanumeric.

So, for example, I can have order numbers like so:

42
Order8
MyOrder
9
Order63

When sorted using ORDER BY orderNumber, I get:

42
9
MyOrder
Order63
Order8

DEMO: http://sqlfiddle.com/#!2/7973e/1

This is not what I want. I want them to be sorted like so:

9
42
MyOrder
Order8
Order63

I want them to be lexicographical for the strings, but numeric for the numbers. I thought of something that might work:

ORDER BY IFNULL(NULLIF(CAST(orderNumber AS SIGNED), 0), orderNumber)

DEMO: http://sqlfiddle.com/#!2/7973e/2

But alas, I still get the same results (as the numbers are then re-cast back to strings). How can I sort these values in the way that I want? If only there was some way to "convert" the strings into a sort of numerical value.

score 4 · Answer 1 · answered Aug 17 '12 at 18:30

You could try padding the order number with zeros when you see a numeric value. See this, for example:

http://sqlfiddle.com/#!2/7973e/21

SELECT  
  CASE 
    WHEN CAST(orderNumber AS SIGNED) != 0 THEN LPAD(orderNumber, 10, '0')
    ELSE orderNumber 
  END as padded, 
  orders.*
FROM orders
ORDER BY
  padded

Results in :

|     PADDED | ORDERID | ORDERNUMBER |
--------------------------------------
| 0000000009 |       4 |           9 |
| 0000000042 |       1 |          42 |
|    MyOrder |       3 |     MyOrder |
|    Order63 |       5 |     Order63 |
|     Order8 |       2 |      Order8 |

Full disclosure: I'm the author of SQL Fiddle.

score 2 · Accepted Answer · answered Aug 17 '12 at 18:30

2

You can get close to what you want with the following:

ORDER BY CASE WHEN CONVERT(OrderNumber, SIGNED INTEGER)= 0 
         THEN 1e50
         ELSE CONVERT(OrderNumber, SIGNED INTEGER)
         END ASC, OrderNumber ASC

However, it would need more work if you need to sort a mixed (text/number) order number by its last digits (e.g. Order63, Order8).

answered Aug 17 '12 at 18:30

Holger Brandt

4,324
1
20
35

Ah, so you're just making the strings into huge numbers so they are sorted to the end, clever. I don't care too much about sorting Order63 and Order8. – gen_Eric Aug 17 '12 at 18:36
Wound't `IFNULL(NULLIF(CAST(orderNumber AS SIGNED), 0), 1e50)` work also, instead of `CASE WHEN ... THEN ... ELSE ... END`? – gen_Eric Aug 17 '12 at 18:39
@Rocket, Yes, that would work as well. The main point here is, as you guessed, making strings into a huge number. – Holger Brandt Aug 17 '12 at 18:41
I found a really hacky solution for Order63, and Order8. `CAST(REVERSE(CAST(REVERSE(orderNumber) AS SIGNED)) AS SIGNED)` – gen_Eric Aug 17 '12 at 18:55

Sorting field by numerical value and lexicographical value

2 Answers2

Linked