From bcschnei@attbi.com Fri May 23 20:23:27 2003 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 566BD92506F; Fri, 23 May 2003 16:23:27 -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 16489-04; Fri, 23 May 2003 16:23:25 -0400 (EDT) Received: from rwcrmhc52.attbi.com (rwcrmhc52.attbi.com [216.148.227.88]) by developer.postgresql.org (Postfix) with ESMTP id 510F492507B; Fri, 23 May 2003 16:23:25 -0400 (EDT) Received: from micronpc (unknown[207.189.110.29]) by attbi.com (rwcrmhc52) with SMTP id <2003052320232405200ohbuse> (Authid: bcschnei@attbi.com); Fri, 23 May 2003 20:23:24 +0000 Reply-To: From: "Ben Schneider" To: , Subject: Case Insensitive Searching? Date: Fri, 23 May 2003 13:24:36 -0700 Message-ID: <001301c32169$54d90fa0$5011a8c0@micronpc> MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_NextPart_000_0014_01C3212E.A87A37A0" X-Priority: 3 (Normal) X-MSMail-Priority: Normal X-Mailer: Microsoft Outlook, Build 10.0.4510 Importance: Normal X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1165 X-Virus-Scanned: by amavisd-new X-Archive-Number: 200305/219 X-Sequence-Number: 7070 This is a multi-part message in MIME format. ------=_NextPart_000_0014_01C3212E.A87A37A0 Content-Type: text/plain; charset="us-ascii" Content-Transfer-Encoding: quoted-printable 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. =20 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.) =20 So the question is, how do I perform a case insensitive search? Is there a DB setting I can set to ignore the case? =20 If not, then an example of some PHP code that can do this would be of a great help. =20 Thanks, Ben =20 ------=_NextPart_000_0014_01C3212E.A87A37A0 Content-Type: text/html; charset="us-ascii" Content-Transfer-Encoding: quoted-printable

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. Ho= wever 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.

 

Thanks,

Ben

 

------=_NextPart_000_0014_01C3212E.A87A37A0-- From bruno@wolff.to Sun May 25 13:54:26 2003 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 953D29256D6 for ; Sun, 25 May 2003 09:54:26 -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 01449-09 for ; Sun, 25 May 2003 09:54:26 -0400 (EDT) Received: from wolff.to (wolff.to [66.93.249.74]) by developer.postgresql.org (Postfix) with SMTP id 9A91A9256C6 for ; Sun, 25 May 2003 09:54:25 -0400 (EDT) Received: (qmail 1193 invoked by uid 500); 25 May 2003 13:56:30 -0000 Date: Sun, 25 May 2003 08:56:30 -0500 From: Bruno Wolff III To: Ben Schneider Cc: pgsql-novice@postgresql.org, pgsql-php@postgresql.org Subject: Re: [PHP] Case Insensitive Searching? Message-ID: <20030525135630.GB1057@wolff.to> Mail-Followup-To: Ben Schneider , pgsql-novice@postgresql.org, pgsql-php@postgresql.org References: <001301c32169$54d90fa0$5011a8c0@micronpc> Mime-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Disposition: inline In-Reply-To: <001301c32169$54d90fa0$5011a8c0@micronpc> User-Agent: Mutt/1.5.4i X-Virus-Scanned: by amavisd-new X-Archive-Number: 200305/217 X-Sequence-Number: 7068 On Fri, May 23, 2003 at 13:24:36 -0700, Ben Schneider wrote: > > 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 you were using "like", you can use "ilike" instead. There is also a case insensitive version of the regular expression pattern matching operator. In some cases you may want to use the "lower" function to get the data in just one case before doing a comparison. Without seeing how you are doing your searches now it is hard to provide specifics on what to do to make them case insensitive. From ernest@vogelsinger.at Sun May 25 19:50:55 2003 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/ From josh@agliodbs.com Sun May 25 20:01:11 2003 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 0D7829251F2; Sun, 25 May 2003 16:01:11 -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 16971-10; Sun, 25 May 2003 16:00:43 -0400 (EDT) Received: from davinci.ethosmedia.com (unknown [209.10.40.251]) by developer.postgresql.org (Postfix) with ESMTP id AC2939251D9; Sun, 25 May 2003 16:00:42 -0400 (EDT) Received: from [63.195.55.98] (HELO spooky) by davinci.ethosmedia.com (CommuniGate Pro SMTP 4.0.2) with ESMTP id 3067370; Sun, 25 May 2003 13:00:45 -0700 Content-Type: text/plain; charset="iso-8859-1" From: Josh Berkus Organization: Aglio Database Solutions To: , , Subject: Re: Case Insensitive Searching? Date: Sun, 25 May 2003 12:58:54 -0700 User-Agent: KMail/1.4.3 References: <001301c32169$54d90fa0$5011a8c0@micronpc> In-Reply-To: <001301c32169$54d90fa0$5011a8c0@micronpc> MIME-Version: 1.0 Content-Transfer-Encoding: 8bit Message-Id: <200305251258.54689.josh@agliodbs.com> X-Virus-Scanned: by amavisd-new X-Archive-Number: 200305/226 X-Sequence-Number: 7077 Ben, > So the question is, how do I perform a case insensitive search? Is there a > DB setting I can set to ignore the case? To the latter: No. To the former, there are four ways that you can do a case-insensitive search in PostgreSQL: 1) Using ILIKE: SELECT * FROM sometable WHERE textfield ILIKE 'value%'; 2) Using Regexp operators (see Functions and Operators in the docs): SELECT * FROM sometable WHERE textfield ~* 'value'; 3) Using UPPER() or LOWER() to change the case of the field before comparison; this approach can be better than 1) or 2) because these functions may be indexed, and thus if you are doing a "begins with" or "exact match" search your query may be indexed: SELECT * FROM sometable WHERE UPPER(textfield) LIKE (UPPER('value') || '%'); 4) If most of your searches are "anywhere in field" searches on large text fields, I'd reccomend a look at the two "full text search" tools available in PostgreSQL, one in the /contrib of your source, the second from openFTS.org. Overally, I would strongly recommend that you buy and read an introcductory PostgreSQL book before proceeding further with your project. -- Josh Berkus Aglio Database Solutions San Francisco