Message-ID: <3F8D6B03.83A186EE@nsd.ca>
Date: Wed, 15 Oct 2003 11:42:59 -0400
From: Jean-Luc Lachance <jllachan@nsd.ca>
MIME-Version: 1.0
To: "George A.J" <jinujosein@yahoo.com>
Cc: pgsql-sql@postgresql.org
Subject: Re: Sorting problem
References: <20031015033054.50872.qmail@web14908.mail.yahoo.com>
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit

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