0

I have some values in a mysql database, which when using ORDER BY ASC they are displayed like this

178mm Diamond Core Drill
187mm Diamond Core Drill
22mm Diamond Core Drill
28mm Diamond Core Drill
32mm Diamond Core Drill

This is due to the ordering of the very first digit. When really it should be like this

22mm Diamond Core Drill
28mm Diamond Core Drill
32mm Diamond Core Drill
178mm Diamond Core Drill
187mm Diamond Core Drill

Is there any way to order the field by just the first full number in the string, eg 22, 28, 32 etc...

Cheers.

Sylvain Leroux
  • 50,096
  • 7
  • 103
  • 125
user2564926
  • 45
  • 6
  • 1
    You can use ORDER BY column+0, but consider normalising you data. – Strawberry Jul 09 '13 at 14:32
  • possible duplicate of [MySQL 'Order By' - sorting alphanumeric correctly](http://stackoverflow.com/questions/8557172/mysql-order-by-sorting-alphanumeric-correctly) – Ken White Jul 09 '13 at 16:22

2 Answers2

0

The ORDER BY column+0 as suggested in comment by @Strawberry works.

But as of myself, I prefer explicit CAST for code self-documentation (this will prevent future "optimization" of your code by someone deleting that "pointless" +0 ;):

mysql> SELECT c,CAST(c AS DECIMAL),c+0 from T order by CAST(c AS DECIMAL) ASC;
+--------------------------+--------------------+------+
| c                        | CAST(c AS DECIMAL) | c+0  |
+--------------------------+--------------------+------+
| 22mm Diamond Core Drill  |                 22 |   22 |
| 28mm Diamond Core Drill  |                 28 |   28 |
| 32mm Diamond Core Drill  |                 32 |   32 |
| 178mm Diamond Core Drill |                178 |  178 |
| 187mm Diamond Core Drill |                187 |  187 |
+--------------------------+--------------------+------+
5 rows in set, 15 warnings (0.00 sec)

Please note this will produce warnings 1292 "Truncated incorrect ... value !

Sylvain Leroux
  • 50,096
  • 7
  • 103
  • 125
0

Assuming column is called product_name (column size 100) and table is called products. How about 

SELECT product_name FROM products ORDER BY LPAD(TRIM(product_name),100,'0') ASC ?
Parag
  • 51
  • 1