Обсуждение: Case insensitive ORDER BY

Is there a way to make ORDER BY case insensitive?

Thanks!
Mark

Mark Lubratt wrote:

> Is there a way to make ORDER BY case insensitive?
>
> Thanks!
> Mark
>
>
> ---------------------------(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

Does the following work?

...  ORDER BY UPPER(some_string)




Nick

--On Thursday, March 18, 2004 10:04:35 -0600 Mark Lubratt
<mark.lubratt@indeq.com> wrote:

> Is there a way to make ORDER BY case insensitive?
order by lower(string);


>
> Thanks!
> Mark
>
>
> ---------------------------(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
>



--
Larry Rosenman                     http://www.lerctr.org/~ler
Phone: +1 972-414-9812                 E-Mail: ler@lerctr.org
US Mail: 1905 Steamboat Springs Drive, Garland, TX 75044-6749

Mark Lubratt wrote:
> Is there a way to make ORDER BY case insensitive?

Sure:

    ORDER BY upper(col)

--
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073

dear expert...

why does CLAUSE ORDER BY in POSTAGE SQL must have CASE SENSITIVE, what is
the basic thought? isn't it ORDER BY function is only just a filter
shorting?


regards...


Alam Surya
-------------------
YM : alam_surya
ICQ: 2866676126

Uppss....sorry... i mean POSTGRESQL


>----- Original Message -----
>From: "Alam Surya" <alam_it@csahome.com>
>To: <pgsql-general@postgresql.org>
>Cc: <id-postgresql@yahoogroups.com>
>Sent: Friday, March 19, 2004 10:48 AM
>Subject: Re: [GENERAL] Case insensitive ORDER BY
>
>
> dear expert...
>
> why does CLAUSE ORDER BY in POSTAGE SQL must have CASE SENSITIVE, what is
> the basic thought? isn't it ORDER BY function is only just a filter
> shorting?
>

On Fri, Mar 19, 2004 at 03:48:09 +0000,
  Alam Surya <alam_it@csahome.com> wrote:
> dear expert...
>
> why does CLAUSE ORDER BY in POSTAGE SQL must have CASE SENSITIVE, what is
> the basic thought? isn't it ORDER BY function is only just a filter
> shorting?

You can use functions in an ORDER BY clause. If you want case insensitive
ordering, order by lower (or upper) of your key.

> why does CLAUSE ORDER BY in POSTAGE SQL must have CASE SENSITIVE, what is
> the basic thought? isn't it ORDER BY function is only just a filter
> shorting?

If you were a bond broker, you would probably consider a bond
rated 'AAa' different from one rated 'AAA'.

Case sensitivity is significant for many types of data, that's why
databases should always treat data as case sensitive by default.

For those applications where it doesn't matter, there are case-insensitive
searching and ordering options in PostgreSQL, and if you really don't
need the data in upper/lower case you can always force it to one or the
other with a 'before insert or update' database trigger.
--
Mike Nolan