Received: from localhost (postgresql.org [64.49.215.8]) by postgresql.org (Postfix) with ESMTP id D3F7C47629B for ; Tue, 22 Oct 2002 08:57:00 -0400 (EDT) Received: from morpheus.ian.cx (k162227.ppp.asahi-net.or.jp [218.45.162.227]) by postgresql.org (Postfix) with SMTP id 332B54761D0 for ; Tue, 22 Oct 2002 08:56:58 -0400 (EDT) Received: (qmail 22527 invoked from network); 22 Oct 2002 12:56:58 -0000 Received: from morpheus.ian.cx (HELO ?127.0.0.1?) (10.50.0.225) by morpheus.ian.cx with SMTP; 22 Oct 2002 12:56:58 -0000 Date: Tue, 22 Oct 2002 15:02:15 +0200 From: Maxim Maletsky To: =?ISO-8859-15?Q?Herv=E9?= Piedvache Subject: Re: search/select case-insensitivly. Cc: Zhidian Du , pgsql-php@postgresql.org Reply-To: maxim@php.net Organization: PHPBeginner In-Reply-To: <20021022091245.50B5641812@mailer.elma.fr> References: <1035274378.6376.105.camel@kant.mcmillan.net.nz> <20021022091245.50B5641812@mailer.elma.fr> Message-Id: <20021022145714.4DAF.MAXIM@php.net> MIME-Version: 1.0 Content-Type: text/plain; charset="iso-8859-15" Content-Transfer-Encoding: quoted-printable X-Mailer: Becky! ver. 2.05.05 X-Virus-Scanned: by AMaViS new-20020517 X-Archive-Number: 200210/50 X-Sequence-Number: 1565 As long as I know, some databases will find John even if you ask for JOHN. If not, then here is your SELECT (for mySQL). $sql =3D "SELECT * WHERE UPPER(name) =3D UPPER('$name')"; Your problem is solved now. --=20 Maxim Maletsky maxim@php.net www.PHPBeginner.com // where PHP Begins Herv=E9 Piedvache wrote... : > The best way is to always save data in your database in the same format .= ..=20 > upper or lower ... then create an index on your field classicaly ... >=20 > CREATE INDEX ix_name ON mytable(name); > SELECT name FROM mytable WHERE name =3D '" . strtolower($Name) ."'; >=20 > 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=3Dlower(name); > to get all the old data in the good format ... > Do not forget to vacuum the table after that ! >=20 > Regards, >=20 > Le Mardi 22 Octobre 2002 10:12, Andrew McMillan a =E9crit : > > 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 =3D '$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) =3D '" . 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. >=20 > --=20 > Herv=E9 Piedvache >=20 > Elma Ing=E9nierie Informatique > 6 rue du Faubourg Saint-Honor=E9 > F-75008 - Paris - France > Tel. 33-144949901 > Fax. 33-144949902 >=20 > ---------------------------(end of broadcast)--------------------------- > TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org