Select <-> Case Insensitive

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 thinks himself the wisest is generally the greatest fool"-- Francis Bacon

Marcelo Pereira wrote: