35

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
  • 4,925
  • 6
  • 27
  • 45
ntan
  • 351
  • 1
  • 3
  • 3

6 Answers6

63

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,369
  • 25
  • 36
29
SELECT  *
FROM    mytable
ORDER BY
        CAST(mycol AS DECIMAL)
Quassnoi
  • 398,504
  • 89
  • 603
  • 604
  • @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
15

Here is the solution

SELECT * FROM MyTable ORDER BY ABS(MyCol);
aizaz
  • 3,030
  • 9
  • 24
  • 56
7

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
  • 5,244
  • 1
  • 25
  • 46
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