X-Original-To: pgsql-novice@postgresql.org Received: from localhost (developer.postgresql.org [64.117.224.193]) by developer.postgresql.org (Postfix) with ESMTP id 9E8D89257D1; Sun, 25 May 2003 15:50:55 -0400 (EDT) Received: from developer.postgresql.org ([64.117.224.193]) by localhost (developer.postgresql.org [64.117.224.193:10024]) (amavisd-new) with ESMTP id 15731-06; Sun, 25 May 2003 15:50:54 -0400 (EDT) Received: from relay11.austria.eu.net (relay11.Austria.eu.net [193.154.160.115]) by developer.postgresql.org (Postfix) with ESMTP id 305839257CE; Sun, 25 May 2003 15:50:54 -0400 (EDT) Received: from sisyphus.vogelsinger.at (vogelsinger.at [193.154.189.26]) by relay11.austria.eu.net (8.12.9/8.12.0.Beta10) with ESMTP id h4PJooc8015798; Sun, 25 May 2003 21:50:50 +0200 Message-Id: <5.1.1.6.2.20030525214642.03109ca8@mail.vogelsinger.at> X-Sender: homebird@mail.vogelsinger.at X-Mailer: QUALCOMM Windows Eudora Version 5.1.1 Date: Sun, 25 May 2003 21:51:22 +0200 To: From: Ernest E Vogelsinger Subject: Re: [PHP] Case Insensitive Searching? Cc: , In-Reply-To: <001301c32169$54d90fa0$5011a8c0@micronpc> Mime-Version: 1.0 Content-Type: text/plain; charset="us-ascii" X-Virus-Scanned: by amavisd-new X-Archive-Number: 200305/229 X-Sequence-Number: 7080 At 22:24 23.05.2003, Ben Schneider said: --------------------[snip]-------------------- >I am working on a project where I am creating a PHP front end to a >Postgres database. The schema is new but the data is old. Meaning I had to >convert the data from the old Informix DB. > >Anyway all of the existing data is in all upper case. However new data >going in will be in both upper and lower case.(As requested by the customer.) > >So the question is, how do I perform a case insensitive search? Is there a >DB setting I can set to ignore the case? > >If not, then an example of some PHP code that can do this would be of a >great help. --------------------[snip]-------------------- There are a couple of ways to do that, it depends on your likes/dislikes as well as on performance in your specific case. You could simply use lower() or upper() in your query constraints: .... WHERE lower(column_name) = lower('search_value') Use the ILIKE operator (case insensitive LIKE): .... WHERE column_name ILIKE '%search_value%' However I did some timings and noticed that ILIKE is appox. 10 times slower than LIKE. Maybe a problem on my side, but... LIKE constructs usually tend to NOT use indexes. To have an index at hand for a lower() search, create an index using lower() values: CREATE INDEX id_lower_content ON mytable(lower(column_name)) -- >O Ernest E. Vogelsinger (\) ICQ #13394035 ^ http://www.vogelsinger.at/