Received: from localhost (postgresql.org [64.49.215.8]) by postgresql.org (Postfix) with ESMTP id 1F797475F27 for ; Tue, 22 Oct 2002 04:13:01 -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 78631475DDC for ; Tue, 22 Oct 2002 04:12:59 -0400 (EDT) Received: from 127.0.0.1 (ident=unknown) by kant.catalyst.net.nz with esmtp (masqmail 0.2.11) id 183u9q-46l-00; Tue, 22 Oct 2002 21:12:58 +1300 Subject: Re: search/select case-insensitivly. From: Andrew McMillan To: Zhidian Du Cc: pgsql-php@postgresql.org In-Reply-To: References: Content-Type: text/plain Organization: Message-Id: <1035274378.6376.105.camel@kant.mcmillan.net.nz> Mime-Version: 1.0 X-Mailer: Ximian Evolution 1.1.1 (Preview Release) Date: 22 Oct 2002 21:12:58 +1300 Content-Transfer-Encoding: 7bit X-Virus-Scanned: by AMaViS new-20020517 X-Archive-Number: 200210/46 X-Sequence-Number: 1561 On Tue, 2002-10-22 at 17:01, Zhidian Du wrote: > I want a PHP program to search case-insensitivly. > > for example: > select Name from mytable where Name = '$Name'; > > > Here $Name is what users' input maybe JOHN, john. How to let it match John > in table and find that record? Although you can simply do as another poster said and use the ILIKE operator, there are a few things you may want to consider. You can also do something like: "SELECT name FROM mytable WHERE lower(name) = '" . strtolower($Name) . "'; " This means that PostgreSQL will use an index, if there is an index on lower(name): CREATE INDEX lcname ON mytable( lower(name) ); This will give you the most efficient access if you have many records in 'mytable', whereas ILIKE will require a full-table scan. Regards, 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/ ---------------------------------------------------------------------