Thread: Select <-> Case Insensitive
Hello All, How can I do a query make the `order' statment sort the tuples without looking for the `case' ? I have a table like this: cod | Description ----+---------------- 1 | A 2 | B 3 | C 4 | a 5 | b 6 | c I would like to do a `select * from thistable order by description' that returns the following: cod | Description ----+---------------- 1 | A 4 | a 2 | B 5 | b 3 | C 6 | c How can I do it? [Ps.: Sorry by this simply question, but I couldn't find anything at the man pages!!] Thanks in advance. Marcelo Pereira -- Remember that only God and Esc+:w saves. __ (_.\ Marcelo Pereira | / / ___ | / (_/ _ \__ Matematica/99 - IMECC | _______\____/_\___)___Unicamp_______________/
Marcelo Pereira writes > > How can I do a query make the `order' statment sort the tuples without > looking for the `case' ? > I'm pretty sure a search of the archives would probably turn up the answer, incase not though. ORDER BY lower(field)
Hi, The query SELECT * from mytable order by lower(description); could solve your problem. test=# SELECT * from mycase order by lower(descr); code | descr ------+------- 1 | A 4 | a 2 | B 5 | b 3 | C 6 | c Regards, Devrim On Fri, 22 Feb 2002, Marcelo Pereira wrote: > Hello All, > > How can I do a query make the `order' statment sort the tuples without > looking for the `case' ? > > I have a table like this: > > cod | Description > ----+---------------- > 1 | A > 2 | B > 3 | C > 4 | a > 5 | b > 6 | c > > I would like to do a `select * from thistable order by description' that > returns the following: > > cod | Description > ----+---------------- > 1 | A > 4 | a > 2 | B > 5 | b > 3 | C > 6 | c > > How can I do it? > > [Ps.: Sorry by this simply question, but I couldn't find anything at the > man pages!!] > > Thanks in advance. > > Marcelo Pereira > > -- Remember that only God and Esc+:w saves. > __ > (_.\ Marcelo Pereira | > / / ___ | > / (_/ _ \__ Matematica/99 - IMECC | > _______\____/_\___)___Unicamp_______________/ > > > ---------------------------(end of broadcast)--------------------------- > TIP 3: if posting/reading through Usenet, please send an appropriate > subscribe-nomail command to majordomo@postgresql.org so that your > message can get through to the mailing list cleanly > -- Devrim GUNDUZ devrim@oper.metu.edu.tr devrim.gunduz@linux.org.tr devrimg@tr.net Web : http://devrim.oper.metu.edu.tr ------------------------------------------------------------------
Devrim GUNDUZ wrote: > The query > > SELECT * from mytable order by lower(description); > > could solve your problem. I seem to recall (From C/C++ at least) that you should upcase text for case insensitive comparisons, as with some languages there is no well defined upper->lower conversion, but there is always a unique lower->upper transformation. Is this correct for (Postgres)SQL locale support as well? -Mark -- Mark Rae Tel: +44(0)20 7074 4648 Inpharmatica Fax: +44(0)20 7074 4700 m.rae@inpharmatica.co.uk http://www.inpharmatica.co.uk/
Ok, Thanks Marcelo Pereira -- Remember that only God and Esc+:w saves. __ (_.\ Marcelo Pereira | / / ___ | / (_/ _ \__ Matematica/99 - IMECC | _______\____/_\___)___Unicamp_______________/ --- Arguile, with his fast fingers, wrote: :> Marcelo Pereira writes :> > :> > How can I do a query make the `order' statment sort the tuples without :> > looking for the `case' ? :> > :> :> I'm pretty sure a search of the archives would probably turn up the answer, :> incase not though. :> :> ORDER BY lower(field) :> :> :>
Nice!! Thanks!! Marcelo Pereira -- Remember that only God and Esc+:w saves. __ (_.\ Marcelo Pereira | / / ___ | / (_/ _ \__ Matematica/99 - IMECC | _______\____/_\___)___Unicamp_______________/ --- Devrim GUNDUZ, with his fast fingers, wrote: :> :> Hi, :> :> The query :> :> SELECT * from mytable order by lower(description); :> :> could solve your problem. :> :> test=# SELECT * from mycase order by lower(descr); :> code | descr :> ------+------- :> 1 | A :> 4 | a :> 2 | B :> 5 | b :> 3 | C :> 6 | c :> :> Regards, :> :> Devrim :> :> On Fri, 22 Feb 2002, Marcelo Pereira wrote: :> :> > Hello All, :> > :> > How can I do a query make the `order' statment sort the tuples without :> > looking for the `case' ? :> > :> > I have a table like this: :> > :> > cod | Description :> > ----+---------------- :> > 1 | A :> > 2 | B :> > 3 | C :> > 4 | a :> > 5 | b :> > 6 | c :> > :> > I would like to do a `select * from thistable order by description' that :> > returns the following: :> > :> > cod | Description :> > ----+---------------- :> > 1 | A :> > 4 | a :> > 2 | B :> > 5 | b :> > 3 | C :> > 6 | c :> > :> > How can I do it? :> > :> > [Ps.: Sorry by this simply question, but I couldn't find anything at the :> > man pages!!] :> > :> > Thanks in advance. :> > :> > Marcelo Pereira :> > :> > -- Remember that only God and Esc+:w saves. :> > __ :> > (_.\ Marcelo Pereira | :> > / / ___ | :> > / (_/ _ \__ Matematica/99 - IMECC | :> > _______\____/_\___)___Unicamp_______________/ :> > :> > :> > ---------------------------(end of broadcast)--------------------------- :> > TIP 3: if posting/reading through Usenet, please send an appropriate :> > subscribe-nomail command to majordomo@postgresql.org so that your :> > message can get through to the mailing list cleanly :> > :> :> -- :> :> Devrim GUNDUZ :> :> devrim@oper.metu.edu.tr :> devrim.gunduz@linux.org.tr :> devrimg@tr.net :> :> Web : http://devrim.oper.metu.edu.tr :> ------------------------------------------------------------------ :> :> :> :>
What about 'ilike' ??? http://www.postgresql.org/idocs/index.php?functions-matching.html mfg ALEX -- ________________________________________________________ Institut fuer Geographie und Regionalforschung Universität Wien Kartografie und Geoinformation Departement of Geography and Regional Research University of Vienna Cartography and GIS Universitaetstr. 7, A-1010 Wien, AUSTRIA Tel: (+43 1) 4277 48644 Fax: (+43 1) 4277 48649 E-mail: pucher@atlas.gis.univie.ac.at FTP: ftp://ftp.gis.univie.ac.at WWW: http://www.gis.univie.ac.at/karto ________________________________________________________ "There is a difference between happiness and wisdom: he that thinks himself the happiest man is really so; but he that thinkshimself the wisest is generally the greatest fool"-- Francis Bacon Marcelo Pereira wrote: >Hello All, > >How can I do a query make the `order' statment sort the tuples without >looking for the `case' ? > >I have a table like this: > >cod | Description >----+---------------- > 1 | A > 2 | B > 3 | C > 4 | a > 5 | b > 6 | c > >I would like to do a `select * from thistable order by description' that >returns the following: > >cod | Description >----+---------------- > 1 | A > 4 | a > 2 | B > 5 | b > 3 | C > 6 | c > >How can I do it? > >[Ps.: Sorry by this simply question, but I couldn't find anything at the >man pages!!] > >Thanks in advance. > >Marcelo Pereira > >-- Remember that only God and Esc+:w saves. > __ > (_.\ Marcelo Pereira | > / / ___ | > / (_/ _ \__ Matematica/99 - IMECC | >_______\____/_\___)___Unicamp_______________/ > > >---------------------------(end of broadcast)--------------------------- >TIP 3: if posting/reading through Usenet, please send an appropriate >subscribe-nomail command to majordomo@postgresql.org so that your >message can get through to the mailing list cleanly > >