Re: 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