Re: please need help: alpha numeric sorting

Good idea, but (there's always a but unfortunately) the problem is that I
can have any number of dots on the t column. t column contains references to
paragraphs in books, and  the paragraph reference can have any dots like
a.b.c.d.e (where a b c d and e are numbers in ascii).

    t
 -------
  12.1
  12.1.1.1
  12.2
  12.1.11
  12.1.2
  12.1.1.1.33.2

I'd like:


    t
 -------
  12.1
  12.1.1
  12.1.1.1.33.2
  12.1.2
  12.1.11
  12.2

thanks all for your help, I really appreciate my users want to see this
column sorted this way in there GUI.

----- Original Message -----
From: "Oliver Elphick" <olly@lfix.co.uk>
To: "Duncan Adams (DNS)" <duncan.adams@vcontractor.co.za>
Cc: <pgsql-novice@postgresql.org>
Sent: Friday, August 02, 2002 2:40 AM
Subject: Re: [NOVICE] please need help: alpha numeric sorting


> 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
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: Have you searched our list archives?
>
> http://archives.postgresql.org