Обсуждение: 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 > >