Page 1 of 1

Sorting Text as a Number

Posted: Fri Dec 27, 2013 8:07 pm
by sam
Hello,

Is there an option on the order by statement that I can use to sort text values like numbers?

I have a varchar column in a table like this:

Code: Select all

dbadmin=> \d tsort;
                                    List of Fields by Tables
  Schema  | Table | Column |    Type     | Size | Default | Not Null | Primary Key | Foreign Key
----------+-------+--------+-------------+------+---------+----------+-------------+-------------
 snowfall | tsort | num    | varchar(10) |   10 |         | f        | f           |
(1 row)
Here's what I get when I use an order by:

Code: Select all

dbadmin=> select * from tsort order by num;
 num
------
 10
 100
 1000
 150
 200
 250
 300
 350
 400
 50
 950
(11 rows)
But I want the results to be:

10
50
100
150
200
250
300
350
400
950
1000

Thank you

Re: Sorting Text as a Number

Posted: Fri Dec 27, 2013 8:26 pm
by JimKnicely
Hi Sam,

Simply convert the VARCHAR to an INT in the ORDER BY clause. All of these methods will do the trick:
  • select * from tsort order by num::int;
    select * from tsort order by to_number(num);
    select * from tsort order by cast (num as int);
Hope this helps!