36

I have a varchar field in my table and I want to sort it. But I need to handle this field as integer. Meaning if sort as text the order is "19,2,20" but I want to get the right order "2,19,20".

Can anyone help me?

Patrick Kostjens
  • 5,065
  • 6
  • 29
  • 46
ntan
  • 361
  • 1
  • 3
  • 3

6 Answers6

65

I somehow didn't manage to run the query with CAST. I was always getting Error Code: 1064 near "DECIMAL" (or other numeric type that I chose). So, I found another way to sort varchar as numbers:

SELECT *
FROM mytable
ORDER BY ABS(mycol)

A bit simpler and works in my case.

yentsun
  • 2,488
  • 27
  • 36
29
SELECT  *
FROM    mytable
ORDER BY
        CAST(mycol AS DECIMAL)
Quassnoi
  • 413,100
  • 91
  • 616
  • 614
  • @Quassnoi: Just wondering, but is CASTing more efficient than ORDER BY mycol+0 – dnagirl Oct 26 '09 at 14:40
  • 3
    `@dnagirl`: `DECIMAL` will silently truncate everything beyond the `[0-9]` range, `mycol + 0` (which implies `CAST AS DOUBLE`) will issue a warning. As for performance, I think they're identical. – Quassnoi Oct 26 '09 at 14:47
  • Abhi Beckert - Can't have a space between CAST and the opening bracket – d3wannabe Apr 09 '17 at 16:15
16

Here is the solution

SELECT * FROM MyTable ORDER BY ABS(MyCol);
aizaz
  • 3,056
  • 9
  • 25
  • 57
8

All other answers use ABS, which converts the values into absolute (positive) values, assuming that the integers are positive. A better solution would be to use * 1:

SELECT * FROM mytable ORDER BY mycol * 1

This to prevent casting negative numbers into positive ones. Inspired by: mysql sort string number

Community
  • 1
  • 1
Guido
  • 6,182
  • 1
  • 29
  • 50
5

You can ABS() for this purpose. ABS() is a mathematical function that returns the absolute (positive) value of the specified expression. So query will be something like this

SELECT * FROM MyTable ORDER BY ABS(MyCol);
4

You Can Order varchar field using this code according to your required

SELECT * FROM mytable ORDER BY ABS(mycol)
Rabesh Lal Shrestha
  • 304
  • 1
  • 6
  • 18