CODEWITHSUNDEEP
mysqlsqlsql-order-by
ntan
ntan

Reputation: 371

mysql order varchar field as integer

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?

Upvotes: 37

Views: 57163

Answers (6)

Quassnoi
Quassnoi

Reputation: 425251

SELECT  *
FROM    mytable
ORDER BY
        CAST(mycol AS DECIMAL)

Upvotes: 30

Guido
Guido

Reputation: 6722

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

Upvotes: 8

aizaz
aizaz

Reputation: 3062

Here is the solution

SELECT * FROM MyTable ORDER BY ABS(MyCol);

Upvotes: 16

user2243219
user2243219

Reputation:

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);

Upvotes: 5

yentsun
yentsun

Reputation: 2568

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.

Upvotes: 66

Rabesh Lal Shrestha
Rabesh Lal Shrestha

Reputation: 304

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

SELECT * FROM mytable ORDER BY ABS(mycol)

Upvotes: 4

Related Questions