From: Jean-Luc Lachance <jllachan@nsd.ca>
To: George A.J <jinujosein@yahoo.com>
Cc: pgsql-sql@postgresql.org
Subject: Re: Sorting problem
Date: Wed, 15 Oct 2003 11:42:59 -0400
Message-ID: <3F8D6B03.83A186EE@nsd.ca> (raw)
References: <20031015033054.50872.qmail@web14908.mail.yahoo.com>
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
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: jllachan@nsd.ca, jinujosein@yahoo.com
Subject: Re: Sorting problem
In-Reply-To: <3F8D6B03.83A186EE@nsd.ca>
* 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