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
>