From: R. van Twisk <r.vantwisk@jongert.nl>
To: pgsql-sql@postgresql.org
Subject: Re: Sorting problem
Date: Thu, 16 Oct 2003 08:25:26 +0200
Message-ID: <001101c393ae$4a00b9e0$fd01000a@IT001> (raw)
In-Reply-To: <3F8D6B03.83A186EE@nsd.ca>
I think what you actually want is natural sorting.
Ries
> -----Oorspronkelijk bericht-----
> Van: pgsql-sql-owner@postgresql.org
> [mailto:pgsql-sql-owner@postgresql.org]Namens Jean-Luc Lachance
> Verzonden: woensdag 15 oktober 2003 17:43
> Aan: George A.J
> CC: pgsql-sql@postgresql.org
> Onderwerp: Re: [SQL] Sorting problem
>
>
> You are obviously not using C locale.
> If you can't change it for some reason, you can use:
>
> select * from accounts order by int4( trim( acno, '#'));
>
> JLL
>
> "George A.J" wrote:
> >
> > hi all,
> > i am using postgres 7.3.2 .i am converitng a mssql database to
> > postgres.
> > now i am facing a strange problem. sorting based on a
> varchar field is
> > not working
> > as expected. the non alphanumeric characters are not
> sorting based on
> > the ascii
> > value of them.
> >
> > i have the following table structure..
> >
> > create table accounts
> > (
> > AcNo varchar (10),
> > Name varchar(100),
> > balance numeric(19,4)
> > )
> >
> > when i used the query select * from accounts order by acno. the
> > result is not correct
> >
> > suppose that the acno field contains values '###1' ,'###2' ,'##10' ,
> > '#100'
> > the sort order in postgres is
> > '###1'
> > '##10'
> > '#100'
> > '###2'
> > But i want the result as follows
> > '###1'
> > '###2'
> > '##10'
> > '#100'
> >
> > that means the ascii value of # should be considered for sorting..
> > what is the problem. is it the behaviour of postgres.
> > do i need to change any configuration. i am using all default
> > configurations
> > or is it a bug...?
> > the problem actually is of < & > operators for varchar.
> >
> > in a simple comparison
> >
> > select '###2' < '##10'
> >
> > returns false but i need true.
> >
> > is there any solution exist. even if i replaced # with any non
> > alphanumeric
> > character the result is same..
> >
> > pls help
> >
> > jinu jose
> >
> >
> ----------------------------------------------------------------------
> > Do you Yahoo!?
> > The New Yahoo! Shopping - with improved product search
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 5: Have you checked our extensive FAQ?
>
> http://www.postgresql.org/docs/faqs/FAQ.html
>
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: r.vantwisk@jongert.nl
Subject: Re: Sorting problem
In-Reply-To: <001101c393ae$4a00b9e0$fd01000a@IT001>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox