Sender: rd@dr15.cnrs.fr
Message-ID: <374AB7A6.D6C313D0@dr15.cnrs.fr>
Date: Tue, 25 May 1999 14:45:58 +0000
From: Roland Dirlewanger <rd@dr15.cnrs.fr>
Organization: CNRS, Delegation Aquitaine et Poitou-Charentes, France.
MIME-Version: 1.0
To: pgsql-interfaces@postgreSQL.org
Subject: case insensitive search in a column
Content-Type: text/plain; charset=iso-8859-1
Content-Transfer-Encoding: 8bit


Hope the following problem is related enough to interfaces  ...

I'm looking for the most efficient way to look through a PGSQL database
with a case insensitive search.

I tried the following queries :
1. select * from mybase where myattr like 'word%';
2. select * from mybase where myattr ~* '^word';

The first one is case-sensitive. The second one matches my needs but is
about 5 or 6 times slower than the first one.

Before I start to convert the whole database in either lowercase or
uppercase, is there a way to fasten up case insensitive searches for
words a the begining of a column or even in the middle of a row ?

Thanks a lot in advance for your help.

--
Roland Dirlewanger
CNRS - Délégation Aquitaine et Poitou-Charentes
Esplanade des Arts et Métiers
33402 TALENCE CEDEX

Mél : rd@dr15.cnrs.fr, Tél : 05.57.35.58.52, Fax : 05.57.35.58.01