Received: from lucas.mandanet.ch (lucas.mandanet.ch [195.15.14.8]) by hub.org (8.8.8/8.8.8) with SMTP id EAA13820 for ; Thu, 17 Sep 1998 04:08:20 -0400 (EDT) (envelope-from jknobl@mandanet.ch) Received: from thomas.mandanet.ch (thomas.mandanet.ch [195.15.14.68]) by lucas.mandanet.ch (NTMail 3.02.13) with ESMTP id qa011248 for ; Thu, 17 Sep 1998 10:13:49 +0100 Message-ID: <3600C4BF.C99176ED@mandanet.ch> Date: Thu, 17 Sep 1998 10:13:51 +0200 From: Jerome Knobl Organization: MandaNET X-Mailer: Mozilla 4.04 [en] (WinNT; I) MIME-Version: 1.0 To: Herouth Maoz CC: pgsql-sql@postgreSQL.org Subject: Re: [SQL] How to Make Case InSensitive??? References: <19980916134810.21155.rocketmail@send101.yahoomail.com> Content-Type: text/plain; charset=us-ascii Content-Transfer-Encoding: 7bit I working with a table of about 70'000 records and I made 2 tests about the optimisation of ~* : With ~* : ---------- % time -p psql mayer98 -c "select * from artistes where nom ~* 'picasso';" ref|nom |nom_court |type|nationalite -----+----------------------------+-------------+----+----------- 76160|PICASSO Pablo, 1881-1973 |PICASSO | 0|E 76161|PICASSO Loulou, *1958 |PICASSOLoulou| 0|F 76162|(A) PICASSO Pablo, 1881-1973|PICASSOPablo | 6|E (3 rows) real 11.52 * user 0.02 sys 0.01 With upper(...) and like upper(%...%) -------------------------------------- % time -p psql mayer98 -c "select * from artistes where upper(nom) like upper('%PICASSO%');" ref|nom |nom_court |type|nationalite -----+----------------------------+-------------+----+----------- 76160|PICASSO Pablo, 1881-1973 |PICASSO | 0|E 76161|PICASSO Loulou, *1958 |PICASSOLoulou| 0|F 76162|(A) PICASSO Pablo, 1881-1973|PICASSOPablo | 6|E (3 rows) real 4.44 * user 0.03 sys 0.00 Relsult: it seem to be 2.6 time more optimised to use upper(...) like upper('%...%') ??? JK Herouth Maoz wrote: > At 16:14 +0200 on 16/9/98, Sferacarta Software wrote: > > > regular expression operators ^ stand for first character and $ stand > > for last character: > > > > ~* '^india$' > > > > matches India > > india > > INDIA > > > > but not Indiana > > New India > > Another way to think of it is like this: > > 'india' is eqivalent to "contains 'india'" > '^india' is eqivalent to "starts with 'india'" > 'india$' is eqivalent to "ends with 'india'" > '^india$' is eqivalent to "is exactly 'india'" > > Herouth > > -- > Herouth Maoz, Internet developer. > Open University of Israel - Telem project > http://telem.openu.ac.il/~herutma