0

I need to order a varchar column in the following way: 100, 110, 120, [...] 1490, 1495, 1495.1, 1495.2, 1495.3, etc.

With help from another question, I created this function:

    CREATE FUNCTION isNumeric(value VARCHAR(1024))
    RETURNS TINYINT(1) DETERMINISTIC
    RETURN value REGEXP '^(-|\\+)?([0-9]+\\.[0-9]*|[0-9]*\\.[0-9]+|[0-9]+)$';

And then added this to my query:

    ORDER BY isNumeric(ItemCode) DESC, CASE WHEN isNumeric(ItemCode) = 1 THEN CAST(ItemCode as UNSIGNED) ELSE NULL END, ItemCode

It worked great. Unfortunately, when I tried this on the live server, I wasn't able to create the function. "Column count of mysql.proc is wrong. Expected 20, found 16. Created with MySQL 50067, now running 50147. Please use mysql_upgrade to fix this error." My boss says we can't run an update in case it breaks something, so I was hoping someone could help me achieve the same thing without a function. Thanks!

DERNERSERFT
  • 110
  • 10
  • 1
    How about [*mysql order varchar field as integer*](http://stackoverflow.com/questions/1625166/mysql-order-varchar-field-as-integer) and [*How to ORDER BY varchar field as number?*](http://stackoverflow.com/questions/11728683/how-to-order-by-varchar-field-as-number) – M Khalid Junaid Apr 24 '14 at 15:58

1 Answers1

0
    ORDER BY ABS(ItemCode)

That worked for me. Thanks M Khalid.

DERNERSERFT
  • 110
  • 10