Received: from localhost (postgresql.org [64.49.215.8]) by postgresql.org (Postfix) with ESMTP id 31007475F1A for ; Tue, 22 Oct 2002 06:22:57 -0400 (EDT) Received: from kant.catalyst.net.nz (203-79-74-78.adsl.paradise.net.nz [203.79.74.78]) by postgresql.org (Postfix) with ESMTP id CFE81475C26 for ; Tue, 22 Oct 2002 06:22:55 -0400 (EDT) Received: from 127.0.0.1 (ident=unknown) by kant.catalyst.net.nz with esmtp (masqmail 0.2.11) id 183wBU-4SC-00; Tue, 22 Oct 2002 23:22:48 +1300 Subject: Re: search/select case-insensitivly. From: Andrew McMillan To: =?ISO-8859-1?Q?Herv=E9?= Piedvache Cc: Zhidian Du , pgsql-php@postgresql.org In-Reply-To: <20021022091245.50B5641812@mailer.elma.fr> References: <1035274378.6376.105.camel@kant.mcmillan.net.nz> <20021022091245.50B5641812@mailer.elma.fr> Content-Type: text/plain; charset=ISO-8859-1 Organization: Message-Id: <1035282167.6372.153.camel@kant.mcmillan.net.nz> Mime-Version: 1.0 X-Mailer: Ximian Evolution 1.1.1 (Preview Release) Date: 22 Oct 2002 23:22:47 +1300 Content-Transfer-Encoding: 8bit X-Virus-Scanned: by AMaViS new-20020517 X-Archive-Number: 200210/48 X-Sequence-Number: 1563 On Tue, 2002-10-22 at 22:10, Hervé Piedvache wrote: > The best way is to always save data in your database in the same format ... > upper or lower ... then create an index on your field classicaly ... > > CREATE INDEX ix_name ON mytable(name); > SELECT name FROM mytable WHERE name = '" . strtolower($Name) ."'; > > You will ever use the index ... and it's simple to implement ... > You can also easily do an update of your database like : > update mytable set name=lower(name); > to get all the old data in the good format ... > Do not forget to vacuum the table after that ! Yes, of course you can do that - I was assuming there was a reason why the poster wanted case preserved. I generally preserve case for email addresses, for example. Sometimes the LHS of an e-mail address is case sensitive, even though the RHS (domain name) may not be. Searching on such a field, however, I would probably want to search case insensitively. There are other reasons for preserving case, of course - especially the case that people write their names. I usually write my own name McMillan, and it _infuriates_ me when I see software that has translated that to Mcmillan. Lest you think that is easily computed, I know people with names which are correctly capitalised as Macindoe and Macinnes - the capitalisation is idiosyncratic. If you remove the capitalisation when you write those values to the file you are removing potentially valuable information. Finally, you should also consider: update mytable set name=lower(name) WHERE name != lower(name); will require a lot less vacuuming :-) Cheers, Andrew. -- --------------------------------------------------------------------- Andrew @ Catalyst .Net.NZ Ltd, PO Box 11-053, Manners St, Wellington WEB: http://catalyst.net.nz/ PHYS: Level 2, 150-154 Willis St DDI: +64(4)916-7201 MOB: +64(21)635-694 OFFICE: +64(4)499-2267 Survey for free with http://survey.net.nz/ ---------------------------------------------------------------------