Обсуждение: Re: Simple Question: Case sensitivity - Performance?

This is ok and will work BUT won't it be slooowwww ...
Will it be able to use an index or will it scan sequentially???
From DB2 experience it will not be able to use indexes on firstname/secondname
because they will be stored in mixed case - is this true in postgres???

If its possible you could create an index on UPPER(firstname) then this may get
around the
problem

As an aside in DB2 there is the concept of a shared weight index which depending
on
locale lumps lower/upper case characters together so that you don't have to
include
an UPPER in the SQL - and it will use the index. Perhaps postgres can also work
this way?

Regards,

MC







"Abe" <abe@fish.tm> on 10/12/2000 13:08:50

To:   "Hancock, David \(DHANCOCK\)" <DHANCOCK@arinc.com>,
      pgsql-general@postgresql.org
cc:    (bcc: Martin Chantler/CIMG/CVG)
Subject:  Re: [GENERAL] Simple Question: Case sensitivity




Thanks David,

works a treat!

Abe
----- Original Message -----
From: "Hancock, David (DHANCOCK)" <DHANCOCK@arinc.com>
To: "'Abe '" <abe@fish.tm>; <pgsql-general@postgresql.org>
Sent: Sunday, December 10, 2000 12:48 PM
Subject: RE: [GENERAL] Simple Question: Case sensitivity


> Abe: It's an SQL thing or a scripting thing.  It's probably easiest and
> safest in the SQL:
>
>    select firstname, surname from employees
>       where upper(firstname) like upper('%$criteria%') or
>       upper(surname) like upper('%$criteria%')
>
> That is, force the column and the search string to uppercase befor
> comparing, and it won't matter how it's stored in the database.
>
> Cheers!
> --
> David Hancock
>
> -----Original Message-----
> From: Abe
> To: pgsql-general@postgresql.org
> Sent: 12/10/00 7:23 AM
> Subject: [GENERAL] Simple Question: Case sensitivity
>
> This is probably an easy question for most but here goes:
>
> I am using PHP3 and postgres 6.5
>
> I am trying to do a search on a peoples database and it works fine
> except
> for the fact that I want to make it case insensitive as some in the
> database
> are Smith and some are jones.  Is this a scripting thing or can it be
> done
> in my query.
>
> Here is the query:
>
> $sql = "select surname, firstname, title, company, worktel, ext,
> hometel,
> mobile, email, emailtwo from employees where firstname like
> '%$criteria%' or
> surname like '%$criteria%'";
>
> Thanks in advance,
> Abe
>

At 03:43 PM 12/11/2000 +0000, martin.chantler@convergys.com wrote:

>As an aside in DB2 there is the concept of a shared weight index which
>depending
>on
>locale lumps lower/upper case characters together so that you don't have to
>include
>an UPPER in the SQL - and it will use the index. Perhaps postgres can also
>work
>this way?

MS-SQL server and sybase both have an option which allows you to do case
insensitive queries as a database setting. I know that this is not standard
SQL behavior but considering that MS access also treats all of it's queries
in a case insensitive matter I think it makes sense to try and accommodate
people migrating from SQL server/ sybase or up sizing from access.

I have thought of doing one of the following.

1) overload the = operator for varchar, text,bpchar etc. so that it
compares insensitively. Would this seriously disrupt the database? How
would it effect group bys and order bys?

2) Dig into the code and change the varlena functions so that they do a
upper before the strcmp. Again how seriously this would disrupt the rest of
the system.

3) Create a case insensitive locale. Well this certainly seems the cleanest
approach and is unlikely to break the database in any way. I am suprised
nobody has done this yet. Is there a document which describes how to create
locales?

:wq
Tim Uckun
Due Diligence Inc. http://www.diligence.com/   Americas Background
Investigation Expert.
If your company isn't doing background checks, maybe you haven't considered
the risks of a bad hire.