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