Postgres Pro
Downloads
Home   >   mailing lists

Thread: please need help: alpha numeric sorting

please need help: alpha numeric sorting

From
"Raouf"
Date:
Hi Gurus,
 
I need your help for this one:
 
let's say I have 1 field of type VARCHAR that contains numbers (as ascii) and characters like this:
 
 
title
-----------
12.1 cisco ccnp routing
12.2 cisco ccnp remote access
12.11cisco ccnp switching
12.3 cisco ccnp support 
 
 
I'd like to sort that column in alphanumeric order, like this:
 
title
-----------
12.1 cisco ccnp routing
12.2 cisco ccnp remote access
12.3 cisco ccnp support 
12.11cisco ccnp switching
 
 
but if I sort using group by title and order by title asc I have:
 
 
title
-----------
12.1 cisco ccnp routing
12.11cisco ccnp switching
12.2 cisco ccnp remote access
12.3 cisco ccnp support 
12.11cisco ccnp switching
 
where "12.11cisco ccnp switching" is before "12.2 cisco ccnp remote access" because it is smaller.
 
Is it possible  ?
 
thanks for your help

Re: please need help: alpha numeric sorting

From
"Markus Wollny"
Date:
Hello!
 
The resulting sorting order is quite correct in terms of alphanumeric sorting. If you want numeric sorting, you would probably need to separate the leading number from the tailing string, so you had two fields e.g. "chapter_nr" of type float4 and "chapter_title" of type text (if that's what it is). Then you could just order by chapter_nr and would get a numerically correct sort instead of the alphanumerically correct sort. Alphanumeric always goes character per character from left to right and therefore n.11 is coming before n.2 - the tailing 1 after the first 1 doesn't matter, because n.11 is not regarded as n, point and eleven but n, point and two ones in sequence.
 
Regards,
 
    Markus
-----Ursprüngliche Nachricht-----
Von: Raouf [mailto:aimeur@prodigy.net]
Gesendet: Freitag, 2. August 2002 10:49
An: pgsql-novice@postgresql.org
Betreff: [NOVICE] please need help: alpha numeric sorting

Hi Gurus,
 
I need your help for this one:
 
let's say I have 1 field of type VARCHAR that contains numbers (as ascii) and characters like this:
 
 
title
-----------
12.1 cisco ccnp routing
12.2 cisco ccnp remote access
12.11cisco ccnp switching
12.3 cisco ccnp support 
 
 
I'd like to sort that column in alphanumeric order, like this:
 
title
-----------
12.1 cisco ccnp routing
12.2 cisco ccnp remote access
12.3 cisco ccnp support 
12.11cisco ccnp switching
 
 
but if I sort using group by title and order by title asc I have:
 
 
title
-----------
12.1 cisco ccnp routing
12.11cisco ccnp switching
12.2 cisco ccnp remote access
12.3 cisco ccnp support 
12.11cisco ccnp switching
 
where "12.11cisco ccnp switching" is before "12.2 cisco ccnp remote access" because it is smaller.
 
Is it possible  ?
 
thanks for your help

Re: please need help: alpha numeric sorting

From
"Duncan Adams (DNS)"
Date:
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.
-----Original Message-----
From: Markus Wollny [mailto:Markus.Wollny@computec.de]
Sent: Friday, August 02, 2002 10:56 AM
To: Raouf; pgsql-novice@postgresql.org
Subject: Re: [NOVICE] please need help: alpha numeric sorting

Hello!
 
The resulting sorting order is quite correct in terms of alphanumeric sorting. If you want numeric sorting, you would probably need to separate the leading number from the tailing string, so you had two fields e.g. "chapter_nr" of type float4 and "chapter_title" of type text (if that's what it is). Then you could just order by chapter_nr and would get a numerically correct sort instead of the alphanumerically correct sort. Alphanumeric always goes character per character from left to right and therefore n.11 is coming before n.2 - the tailing 1 after the first 1 doesn't matter, because n.11 is not regarded as n, point and eleven but n, point and two ones in sequence.
 
Regards,
 
    Markus
-----Ursprüngliche Nachricht-----
Von: Raouf [mailto:aimeur@prodigy.net]
Gesendet: Freitag, 2. August 2002 10:49
An: pgsql-novice@postgresql.org
Betreff: [NOVICE] please need help: alpha numeric sorting

Hi Gurus,
 
I need your help for this one:
 
let's say I have 1 field of type VARCHAR that contains numbers (as ascii) and characters like this:
 
 
title
-----------
12.1 cisco ccnp routing
12.2 cisco ccnp remote access
12.11cisco ccnp switching
12.3 cisco ccnp support 
 
 
I'd like to sort that column in alphanumeric order, like this:
 
title
-----------
12.1 cisco ccnp routing
12.2 cisco ccnp remote access
12.3 cisco ccnp support 
12.11cisco ccnp switching
 
 
but if I sort using group by title and order by title asc I have:
 
 
title
-----------
12.1 cisco ccnp routing
12.11cisco ccnp switching
12.2 cisco ccnp remote access
12.3 cisco ccnp support 
12.11cisco ccnp switching
 
where "12.11cisco ccnp switching" is before "12.2 cisco ccnp remote access" because it is smaller.
 
Is it possible  ?
 
thanks for your help

Re: please need help: alpha numeric sorting

From
"Raouf"
Date:
Hello Markus,
 
thanks for your reply. Yes I was thinking of splitting my fields in to 2 separate fields but actually I need to have the dot between the numbers, e.g  12.1 or 12.1.2, so actually it won't change anything for me. Do you know if thers's a function that I could use for the sorting ?
 
thanks, 
----- Original Message -----
Sent: Friday, August 02, 2002 2:00 AM
Subject: Re: [NOVICE] please need help: alpha numeric sorting

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.
-----Original Message-----
From: Markus Wollny [mailto:Markus.Wollny@computec.de]
Sent: Friday, August 02, 2002 10:56 AM
To: Raouf; pgsql-novice@postgresql.org
Subject: Re: [NOVICE] please need help: alpha numeric sorting

Hello!
 
The resulting sorting order is quite correct in terms of alphanumeric sorting. If you want numeric sorting, you would probably need to separate the leading number from the tailing string, so you had two fields e.g. "chapter_nr" of type float4 and "chapter_title" of type text (if that's what it is). Then you could just order by chapter_nr and would get a numerically correct sort instead of the alphanumerically correct sort. Alphanumeric always goes character per character from left to right and therefore n.11 is coming before n.2 - the tailing 1 after the first 1 doesn't matter, because n.11 is not regarded as n, point and eleven but n, point and two ones in sequence.
 
Regards,
 
    Markus
-----Ursprüngliche Nachricht-----
Von: Raouf [mailto:aimeur@prodigy.net]
Gesendet: Freitag, 2. August 2002 10:49
An: pgsql-novice@postgresql.org
Betreff: [NOVICE] please need help: alpha numeric sorting

Hi Gurus,
 
I need your help for this one:
 
let's say I have 1 field of type VARCHAR that contains numbers (as ascii) and characters like this:
 
 
title
-----------
12.1 cisco ccnp routing
12.2 cisco ccnp remote access
12.11cisco ccnp switching
12.3 cisco ccnp support 
 
 
I'd like to sort that column in alphanumeric order, like this:
 
title
-----------
12.1 cisco ccnp routing
12.2 cisco ccnp remote access
12.3 cisco ccnp support 
12.11cisco ccnp switching
 
 
but if I sort using group by title and order by title asc I have:
 
 
title
-----------
12.1 cisco ccnp routing
12.11cisco ccnp switching
12.2 cisco ccnp remote access
12.3 cisco ccnp support 
12.11cisco ccnp switching
 
where "12.11cisco ccnp switching" is before "12.2 cisco ccnp remote access" because it is smaller.
 
Is it possible  ?
 
thanks for your help

Re: please need help: alpha numeric sorting

From
"Duncan Adams (DNS)"
Date:
u could try
 
select <field1> || '.' || <field2> as foo from bar
 
all though that still leaves me stuck.
-----Original Message-----
From: Raouf [mailto:aimeur@prodigy.net]
Sent: Friday, August 02, 2002 11:26 AM
To: Duncan Adams (DNS); pgsql-novice@postgresql.org
Subject: Re: [NOVICE] please need help: alpha numeric sorting

Hello Markus,
 
thanks for your reply. Yes I was thinking of splitting my fields in to 2 separate fields but actually I need to have the dot between the numbers, e.g  12.1 or 12.1.2, so actually it won't change anything for me. Do you know if thers's a function that I could use for the sorting ?
 
thanks, 
----- Original Message -----
Sent: Friday, August 02, 2002 2:00 AM
Subject: Re: [NOVICE] please need help: alpha numeric sorting

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.
-----Original Message-----
From: Markus Wollny [mailto:Markus.Wollny@computec.de]
Sent: Friday, August 02, 2002 10:56 AM
To: Raouf; pgsql-novice@postgresql.org
Subject: Re: [NOVICE] please need help: alpha numeric sorting

Hello!
 
The resulting sorting order is quite correct in terms of alphanumeric sorting. If you want numeric sorting, you would probably need to separate the leading number from the tailing string, so you had two fields e.g. "chapter_nr" of type float4 and "chapter_title" of type text (if that's what it is). Then you could just order by chapter_nr and would get a numerically correct sort instead of the alphanumerically correct sort. Alphanumeric always goes character per character from left to right and therefore n.11 is coming before n.2 - the tailing 1 after the first 1 doesn't matter, because n.11 is not regarded as n, point and eleven but n, point and two ones in sequence.
 
Regards,
 
    Markus
-----Ursprüngliche Nachricht-----
Von: Raouf [mailto:aimeur@prodigy.net]
Gesendet: Freitag, 2. August 2002 10:49
An: pgsql-novice@postgresql.org
Betreff: [NOVICE] please need help: alpha numeric sorting

Hi Gurus,
 
I need your help for this one:
 
let's say I have 1 field of type VARCHAR that contains numbers (as ascii) and characters like this:
 
 
title
-----------
12.1 cisco ccnp routing
12.2 cisco ccnp remote access
12.11cisco ccnp switching
12.3 cisco ccnp support 
 
 
I'd like to sort that column in alphanumeric order, like this:
 
title
-----------
12.1 cisco ccnp routing
12.2 cisco ccnp remote access
12.3 cisco ccnp support 
12.11cisco ccnp switching
 
 
but if I sort using group by title and order by title asc I have:
 
 
title
-----------
12.1 cisco ccnp routing
12.11cisco ccnp switching
12.2 cisco ccnp remote access
12.3 cisco ccnp support 
12.11cisco ccnp switching
 
where "12.11cisco ccnp switching" is before "12.2 cisco ccnp remote access" because it is smaller.
 
Is it possible  ?
 
thanks for your help

Re: please need help: alpha numeric sorting

From
"Markus Wollny"
Date:
I don't know of anything - but I'm quite knew to pgsql, too. You might try to do this in your application by parsing the numbers between the dots and the doing a sort.
-----Ursprüngliche Nachricht-----
Von: Raouf [mailto:aimeur@prodigy.net]
Gesendet: Freitag, 2. August 2002 11:26
An: Duncan Adams (DNS); pgsql-novice@postgresql.org
Betreff: Re: [NOVICE] please need help: alpha numeric sorting

Hello Markus,
 
thanks for your reply. Yes I was thinking of splitting my fields in to 2 separate fields but actually I need to have the dot between the numbers, e.g  12.1 or 12.1.2, so actually it won't change anything for me. Do you know if thers's a function that I could use for the sorting ?
 
thanks, 
----- Original Message -----
Sent: Friday, August 02, 2002 2:00 AM
Subject: Re: [NOVICE] please need help: alpha numeric sorting

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.
-----Original Message-----
From: Markus Wollny [mailto:Markus.Wollny@computec.de]
Sent: Friday, August 02, 2002 10:56 AM
To: Raouf; pgsql-novice@postgresql.org
Subject: Re: [NOVICE] please need help: alpha numeric sorting

Hello!
 
The resulting sorting order is quite correct in terms of alphanumeric sorting. If you want numeric sorting, you would probably need to separate the leading number from the tailing string, so you had two fields e.g. "chapter_nr" of type float4 and "chapter_title" of type text (if that's what it is). Then you could just order by chapter_nr and would get a numerically correct sort instead of the alphanumerically correct sort. Alphanumeric always goes character per character from left to right and therefore n.11 is coming before n.2 - the tailing 1 after the first 1 doesn't matter, because n.11 is not regarded as n, point and eleven but n, point and two ones in sequence.
 
Regards,
 
    Markus
-----Ursprüngliche Nachricht-----
Von: Raouf [mailto:aimeur@prodigy.net]
Gesendet: Freitag, 2. August 2002 10:49
An: pgsql-novice@postgresql.org
Betreff: [NOVICE] please need help: alpha numeric sorting

Hi Gurus,
 
I need your help for this one:
 
let's say I have 1 field of type VARCHAR that contains numbers (as ascii) and characters like this:
 
 
title
-----------
12.1 cisco ccnp routing
12.2 cisco ccnp remote access
12.11cisco ccnp switching
12.3 cisco ccnp support 
 
 
I'd like to sort that column in alphanumeric order, like this:
 
title
-----------
12.1 cisco ccnp routing
12.2 cisco ccnp remote access
12.3 cisco ccnp support 
12.11cisco ccnp switching
 
 
but if I sort using group by title and order by title asc I have:
 
 
title
-----------
12.1 cisco ccnp routing
12.11cisco ccnp switching
12.2 cisco ccnp remote access
12.3 cisco ccnp support 
12.11cisco ccnp switching
 
where "12.11cisco ccnp switching" is before "12.2 cisco ccnp remote access" because it is smaller.
 
Is it possible  ?
 
thanks for your help

Re: please need help: alpha numeric sorting

From
Ludwig Lim
Date:
--- "Duncan Adams  (DNS)"
<duncan.adams@vcontractor.co.za> wrote:
> u could try
>
> select <field1> || '.' || <field2> as foo from bar
>
> all though that still leaves me stuck.

-what about
select <field1> || '.' || <field2> as foo
from bar
order by to_number(<field1>,'9999'),
to_number(<field2>,'9999');

Assuming that both <field1> and <field2> are
alphanumeric characters. This forces numeric sorting
by converting <field1> and <field2> to numbers.

ludwig.



__________________________________________________
Do You Yahoo!?
Yahoo! Health - Feel better, live better
http://health.yahoo.com

Re: please need help: alpha numeric sorting

From
Oliver Elphick
Date:
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

Re: please need help: alpha numeric sorting

From
"Raouf"
Date:
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

Re: please need help: alpha numeric sorting

From
Ken Corey
Date:
On Fri, 2002-08-02 at 09:48, Raouf wrote:
> Hi Gurus,
>
> I need your help for this one:
>
> let's say I have 1 field of type VARCHAR that contains numbers (as ascii) and characters like this:
>
>
> title
> -----------
> 12.1 cisco ccnp routing
> 12.2 cisco ccnp remote access
> 12.11cisco ccnp switching
> 12.3 cisco ccnp support
>
>
> I'd like to sort that column in alphanumeric order, like this:
>
> title
> -----------
> 12.1 cisco ccnp routing
> 12.2 cisco ccnp remote access
> 12.3 cisco ccnp support
> 12.11cisco ccnp switching
>
>
> but if I sort using group by title and order by title asc I have:
>
>
> title
> -----------
> 12.1 cisco ccnp routing
> 12.11cisco ccnp switching
> 12.2 cisco ccnp remote access
> 12.3 cisco ccnp support
> 12.11cisco ccnp switching
>
> where "12.11cisco ccnp switching" is before "12.2 cisco ccnp remote access" because it is smaller.
>
> Is it possible  ?
>
> thanks for your help

It is possible (hack at bottom)...but I certainly wouldn't recommend it
because as you get more than 10 rows, you'll *really* see a slow down of
the function.  indexes won't apply, and sorts will be significantly
slowed.

I'd recommend that you have two more columns 'major' and 'minor', and
have these two columns be integers, like this:

drop table a;
create table a (
  major int4,
  minor int4,
  description varchar(255)
);

Now, when inserting into this table, parse the numbers once using your
favorite language, or use a stored function to parse it:
insert into a values(12, 1,'12.1 description1');
insert into a values(12, 2,'12.2 description1');
insert into a values(12,10,'12.10description1');

Now, selecting with an appropriate order is a breeze:

select description from a
order by major, minor;

That way you can have indexes on both major and minor, and your sorts
will be much faster and far more scalable.

For fun, here's the hack that will let you do thwat you wanted in the
first place...(blech!)

HACK FOLLOWS
------------

-- First, create a test bed:
drop table a;
create table a (b varchar(255));
insert into a values ('12.1 testing');
insert into a values ('12.2 testing');
insert into a values ('12.10testing');

-- Now, see if we still have this problem...
select * from a order by b;

-- Okay, now convert the leading number (12.1) into '12.001', so
-- we can then sort numerically, and do so.  Yuck.
select
  *
from a
order by
  -- break out the digits before the '.'
  substring(
     substring(b for 5)
     for position('.' in substring(b for 5))-1)
  -- add the '.' back
  ||'.'||
  -- Turn that back to a string
  to_char(
    -- Turn them to a zero padded number.
    to_number(
      -- Get the digits after the '.'
      substring(
       substring(b for 5)
       from position('.' in substring(b for 5))+1)
      ,'999')
    ,'FM000');

-Ken