Thread

1. Sorting problem

   George A.J <jinujosein@yahoo.com> — 2003-10-15T03:30:54Z

   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

2. Re: Sorting problem

   Stephan Szabo <sszabo@megazone.bigpanda.com> — 2003-10-15T03:39:25Z

   On Tue, 14 Oct 2003, 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.
   
   What OS and locale are you using?  If it's not "C", you're probably
   getting bit by the fact that many natural language collations (such as
   en_US for example) don't consider most of the symbols except as tie
   breakers when sorting.  Unfortunately, to change the locale you need to
   run initdb again with the appropriate locale, something like:
   LANG="C" initdb -D /path/to/dataspace
   
   
   
   

3. Re: Sorting problem

   Jean-Luc Lachance <jllachan@nsd.ca> — 2003-10-15T15:42:59Z

   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
   
   

4. Re: Sorting problem

   R. van Twisk <r.vantwisk@jongert.nl> — 2003-10-16T06:25:26Z

   I think what you actually want is natural sorting.
   
   Ries
   
   > -----Oorspronkelijk bericht-----
   > Van: pgsql-sql-owner@postgresql.org
   > [mailto:pgsql-sql-owner@postgresql.org]Namens Jean-Luc Lachance
   > Verzonden: woensdag 15 oktober 2003 17:43
   > Aan: George A.J
   > CC: pgsql-sql@postgresql.org
   > 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
   >