Skip site navigation (1) Skip section navigation (2)

Re: please need help: alpha numeric sorting

From: "Markus Wollny" <Markus(dot)Wollny(at)computec(dot)de>
To: "Raouf" <aimeur(at)prodigy(dot)net>, <pgsql-novice(at)postgresql(dot)org>
Subject: Re: please need help: alpha numeric sorting
Date: 2002-08-02 08:56:10
Message-ID: 2266D0630E43BB4290742247C8910575014CE32B@dozer.computec.de (view raw or flat)
Thread:
Lists: pgsql-novice
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(at)prodigy(dot)net]
Gesendet: Freitag, 2. August 2002 10:49
An: pgsql-novice(at)postgresql(dot)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

pgsql-novice by date

Next:From: Duncan Adams (DNS)Date: 2002-08-02 09:00:50
Subject: Re: please need help: alpha numeric sorting
Previous:From: RaoufDate: 2002-08-02 08:48:52
Subject: please need help: alpha numeric sorting

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group