Received: from ubik.melog.com.pl (IDENT:qmailr@ubik.melog.com.pl [212.160.91.66]) by hub.org (8.9.3/8.9.3) with SMTP id FAA08585 for ; Fri, 13 Aug 1999 05:06:54 -0400 (EDT) (envelope-from bart@bart.w-wa.pl) Received: (qmail 23659 invoked from network); 13 Aug 1999 09:07:18 -0000 Received: from localhost (HELO bart.w-wa.pl) (bart@127.0.0.1) by localhost with SMTP; 13 Aug 1999 09:07:18 -0000 Sender: bart Message-ID: <37B3E046.278547CA@bart.w-wa.pl> Date: Fri, 13 Aug 1999 11:07:18 +0200 From: Bart Ogryczak Organization: Covert X-Mailer: Mozilla 4.6 [en] (X11; U; Linux 2.2.9 i586) X-Accept-Language: en MIME-Version: 1.0 To: pgsql-sql@postgreSQL.org Subject: Re: [SQL] Searching Text Fields - Case Sensitive? References: <003401bee4d3$42774e20$0200a8c0@mike> <37B2E714.DB904C2B@bart.w-wa.pl> <002801bee560$2e499d30$760e01a3@oucs.ox.ac.uk> Content-Type: text/plain; charset=iso-8859-2 Content-Transfer-Encoding: 7bit Moray McConnachie wrote: > > > SELECT * FROM mydb WHERE description LIKE "%foobar%"; > > > it seems to be case sensitive. Is there a way to do it so it is NOT > > > case-sensitive? > > SELECT * FROM mydb WHERE lower(description) LIKE "%foobar%"; > > Brings up an interesting question. Which is faster, to use the lower/upper > functions as above [...] > or to use the pattern matching case-insensitive operator? LIKE seems to be much simpler then the regex pattern matching, so IMHO, the lower/upper & LIKE combination may be faster for short fields, but probably might be slower on very long text fields (when it has to convert whole thing to lower/upper case, before doing LIKE). Anyway, the differences are probably almost none either way. bart -- bart@bart.w-wa.pl || http://www.bart.w-wa.pl