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