X-Original-To: pgsql-php-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [64.117.224.130]) by svr1.postgresql.org (Postfix) with ESMTP id CD17CD1B8A3 for ; Thu, 4 Sep 2003 02:37:33 +0000 (GMT) Received: from svr1.postgresql.org ([64.117.224.193]) by localhost (neptune.hub.org [64.117.224.130]) (amavisd-new, port 10024) with ESMTP id 81850-01 for ; Wed, 3 Sep 2003 23:37:05 -0300 (ADT) Received: from rwcrmhc12.comcast.net (rwcrmhc12.comcast.net [216.148.227.85]) by svr1.postgresql.org (Postfix) with ESMTP id 6A39BD1B960 for ; Wed, 3 Sep 2003 23:37:01 -0300 (ADT) Received: from bigbertha (h008019444478.ne.client2.attbi.com[24.60.19.116](untrusted sender)) by comcast.net (rwcrmhc12) with SMTP id <2003090402370501400s02j4e>; Thu, 4 Sep 2003 02:37:05 +0000 Message-ID: <005001c3728d$d979a8d0$0000fea9@bigbertha> From: "Luis H" To: References: Subject: Re: Case sensitivity when searching for and displaying data Date: Wed, 3 Sep 2003 22:40:05 -0400 MIME-Version: 1.0 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: 7bit X-Priority: 3 X-MSMail-Priority: Normal X-Mailer: Microsoft Outlook Express 6.00.2800.1158 X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1165 X-Virus-Scanned: by amavisd-new at postgresql.org X-Spam-Status: No, hits=3.0 tagged_above=0.0 required=5.0 tests=BAYES_30, MSGID_OE_SPAM_4ZERO, QUOTED_EMAIL_TEXT, REFERENCES X-Spam-Level: *** X-Archive-Number: 200309/11 X-Sequence-Number: 2288 ----- Original Message ----- From: "Lynna Landstreet" To: Sent: Wednesday, September 03, 2003 6:31 PM Subject: [PHP] Case sensitivity when searching for and displaying data > Hello, > > I've gotten a simple PHP search page working on the artists database that ----- Original Message ----- From: "Lynna Landstreet" To: Sent: Wednesday, September 03, 2003 6:31 PM Subject: [PHP] Case sensitivity when searching for and displaying data > Ideally I'd like the search text to case insensitive, so that if the user > enters "jane smith", "Jane Smith" or "JANE SMITH", they'll still find the > record for Jane Smith. But I'm not sure how to do that. For the searching, I'd read through the pattern matching section of pgsql doc: http://www.postgresql.org/docs/7.3/static/functions-matching.html For simple searches, ILIKE performs a case-insensitive search, and for Regular Expressions, use ~* instead of ~. > Secondly, the case sensitivity thing is also messing up the order of the > displayed results. Right now I have them set to order by lastname, and that > makes any names that begin with a lower case character come at the end of > the list because of the order of the ASCII values of the letters, rather > than where they would normally be in alphabetical order. Does anyone know if > there's a way around this? In terms of sorting, you could use the function lower(string), which converts text to lowercase, or upper(string). So you'd execute a query: select * from table ORDER BY lower(lastname); > > > Thanks, > > Lynna > -- No prob. :) Luis