Re: please need help: alpha numeric sorting - Mailing list pgsql-novice

From Oliver Elphick
Subject Re: please need help: alpha numeric sorting
Date
Msg-id 1028281257.25315.118.camel@linda
Whole thread Raw
In response to Re: please need help: alpha numeric sorting  ("Duncan Adams (DNS)" <duncan.adams@vcontractor.co.za>)
List pgsql-novice
On Fri, 2002-08-02 at 10:00, Duncan Adams (DNS) wrote:
> is there no way around splitting the field.
> i have the same problem, i have ports that i would like to order by numeric.
> my main problem been that some ports are called a1 - a24 and then b1 - b24
> and other devices have ports 1a - 24a, 1b - 24b ect.

You don't need to split the field:

 junk=# select * from f order by t;
   t
-------
 12.1
 12.11
 12.2
(3 rows)

junk=# select * from f order by substr(t,1,strpos(t,'.')-1)::integer,
substr(t,strpos(t,'.')+1,999)::integer;
   t
-------
 12.1
 12.2
 12.11
(3 rows)

Oliver Elphick                                Oliver.Elphick@lfix.co.uk
Isle of Wight, UK
http://www.lfix.co.uk/oliver
GPG: 1024D/3E1D0C1C: CA12 09E0 E8D5 8870 5839  932A 614D 4C34 3E1D 0C1C
                 ========================================
     "All scripture is given by inspiration of God, and is
      profitable for doctrine, for reproof, for correction,
      for instruction in righteousness;"
                                     II Timothy 3:16


pgsql-novice by date:

Previous
From: Ludwig Lim
Date:
Subject: Re: please need help: alpha numeric sorting
Next
From: "Raouf"
Date:
Subject: Re: please need help: alpha numeric sorting