I think what you actually want is natural sorting. Ries
> -----Oorspronkelijk bericht----- > Van: [EMAIL PROTECTED] > [mailto:[EMAIL PROTECTED] Jean-Luc Lachance > Verzonden: woensdag 15 oktober 2003 17:43 > Aan: George A.J > CC: [EMAIL PROTECTED] > 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 > ---------------------------(end of broadcast)--------------------------- TIP 6: Have you searched our list archives? http://archives.postgresql.org
