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(at)lfix(dot)co(dot)uk>
To: "Duncan Adams (DNS)" <duncan(dot)adams(at)vcontractor(dot)co(dot)za>
Cc: <pgsql-novice(at)postgresql(dot)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(dot)Elphick(at)lfix(dot)co(dot)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