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
Show quoted text
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 sensitivityThis 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.