Re: Sorting by numerical order

Steve Atkins wrote:
>
> Couldn't you just use a regex match? foo ~ '-?[0-9]+(\.[0-9]+)?' or
> somesuch.
>
> Cheers,
>  Steve

This helps a lot.  I used a similar regular expression, the substring function, and the order by
clause to do the following:

test=# select * from sort_test order by name;
  name
--------
 10
 aaa
 abc
 abc 1
 abc 10
 abc 2
 abc 20
(7 rows)

test=# select name, substring(name from '^(.*?)([-+]?[0-9]+(\\.[0-9]+)?)?$') as start_string,
substring(name from '[-+]?[0-9]+(\\.[0-9]+)?$')::float as end_number from sort_test order by
start_string, end_number;
  name  | start_string | end_number
--------+--------------+------------
 10     |              |         10
 aaa    | aaa          |
 abc    | abc          |
 abc 1  | abc          |          1
 abc 2  | abc          |          2
 abc 10 | abc          |         10
 abc 20 | abc          |         20
(7 rows)

George Essig