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 ECA05D1B8B0 for ; Thu, 4 Sep 2003 06:17:20 +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 02760-07 for ; Thu, 4 Sep 2003 03:17:09 -0300 (ADT) Received: from houston.familyhealth.com.au (unknown [203.59.48.253]) by svr1.postgresql.org (Postfix) with ESMTP id 75C18D1B8C9 for ; Thu, 4 Sep 2003 03:17:06 -0300 (ADT) Received: from mars (work-40.internal [192.168.0.40]) by houston.familyhealth.com.au (8.12.9/8.12.9) with SMTP id h846H2mo005664; Thu, 4 Sep 2003 14:17:02 +0800 (WST) (envelope-from chriskl@familyhealth.com.au) Message-ID: <036b01c372ac$2dd63420$2800a8c0@mars> From: "Christopher Kings-Lynne" To: "Lynna Landstreet" , References: Subject: Re: Case sensitivity when searching for and displaying data Date: Thu, 4 Sep 2003 14:17:12 +0800 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-Archive-Number: 200309/12 X-Sequence-Number: 2289 > I've gotten a simple PHP search page working on the artists database that > I've been developing these past few months, but I'm running into a few > problems with PostgreSQL's case sensitivity. No, you've been used to MySQL case insensitity methinks... > 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. Use ILIKE: SELECT * FROM table WHERE name ILIKE 'jane smith'; Or LOWER: SELECT * FROM table WHERE LOWER(name) = LOWER('JANE SMITH'); (it'll help you to make a functional index in this case) > 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? Use LOWER: SELECT * FROM table WHERE LOWER(name) = LOWER('JANE SMITH') ORDER BY LOWER(name); Chris