From mike@fieldco.com Thu Aug 12 14:54:25 1999 Received: from funredes.org (funredes.org [205.160.164.9]) by hub.org (8.9.3/8.9.3) with SMTP id KAA22391 for ; Thu, 12 Aug 1999 10:54:25 -0400 (EDT) (envelope-from mike@fieldco.com) Received: from mike (net0port190.tricom.net) by funredes.org (5.x/SMI-SVR4) id AA10209; Thu, 12 Aug 1999 10:56:59 -0400 Message-Id: <003401bee4d3$42774e20$0200a8c0@mike> From: "Mike Field" To: Subject: Searching Text Fields - Case Sensitive? Date: Thu, 12 Aug 1999 10:59:20 -0400 Mime-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_NextPart_000_0031_01BEE4B1.BB65AE20" X-Priority: 3 X-Msmail-Priority: Normal X-Mailer: Microsoft Outlook Express 4.72.3155.0 X-Mimeole: Produced By Microsoft MimeOLE V4.72.3155.0 This is a multi-part message in MIME format. ------=_NextPart_000_0031_01BEE4B1.BB65AE20 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable Hi- If I want to search a text field for a certain word, say 'foobar', = using: 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? Please let me know. Thanks, Mike mike@fieldco.com ------=_NextPart_000_0031_01BEE4B1.BB65AE20 Content-Type: text/html; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable
Hi-
 
If I want to search a text field for a certain word, = say=20 'foobar', using:
 
SELECT * FROM mydb WHERE description LIKE=20 "%foobar%";
 
it seems to be case sensitive.  Is there a way = to do it=20 so it is NOT case-sensitive?
 
Please let me know.
Thanks,
Mike
 
mike@fieldco.com
 
------=_NextPart_000_0031_01BEE4B1.BB65AE20-- From bart@bart.w-wa.pl Thu Aug 12 15:23:27 1999 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 LAA28067 for ; Thu, 12 Aug 1999 11:23:27 -0400 (EDT) (envelope-from bart@bart.w-wa.pl) Received: (qmail 20819 invoked from network); 12 Aug 1999 15:24:06 -0000 Received: from localhost (HELO bart.w-wa.pl) (bart@127.0.0.1) by localhost with SMTP; 12 Aug 1999 15:24:06 -0000 Sender: bart Message-ID: <37B2E714.DB904C2B@bart.w-wa.pl> Date: Thu, 12 Aug 1999 17:24:05 +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> Content-Type: text/plain; charset=iso-8859-2 Content-Transfer-Encoding: 7bit > Mike Field wrote: > Hi- > If I want to search a text field for a certain word, say 'foobar', > using: > 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%"; bart -- bart@bart.w-wa.pl || http://www.bart.w-wa.pl From moray.mcconnachie@computing-services.oxford.ac.uk Fri Aug 13 07:50:45 1999 Received: from oxmail.ox.ac.uk (oxmail3.ox.ac.uk [163.1.2.9]) by hub.org (8.9.3/8.9.3) with ESMTP id DAA96447 for ; Fri, 13 Aug 1999 03:50:45 -0400 (EDT) (envelope-from moray.mcconnachie@computing-services.oxford.ac.uk) Received: from heraldgate1.oucs.ox.ac.uk ([163.1.2.49] helo=frontend1.herald.ox.ac.uk ident=exim) by oxmail.ox.ac.uk with esmtp (Exim 2.10 #1) id 11FC6u-00017v-00 for pgsql-sql@postgresql.org; Fri, 13 Aug 1999 08:50:44 +0100 Received: from moraypc.oucs.ox.ac.uk ([163.1.14.118] helo=moraypc) by frontend1.herald.ox.ac.uk with smtp (Exim 2.02 #1) id 11FC6u-0001g3-00 for pgsql-sql@postgreSQL.org; Fri, 13 Aug 1999 08:50:44 +0100 Message-ID: <002801bee560$2e499d30$760e01a3@oucs.ox.ac.uk> From: "Moray McConnachie" To: References: <003401bee4d3$42774e20$0200a8c0@mike> <37B2E714.DB904C2B@bart.w-wa.pl> Subject: Re: [SQL] Searching Text Fields - Case Sensitive? Date: Fri, 13 Aug 1999 08:48:06 +0100 Organization: Oxford University MIME-Version: 1.0 Content-Type: text/plain; charset="iso-8859-2" Content-Transfer-Encoding: 7bit X-Priority: 3 X-MSMail-Priority: Normal X-Mailer: Microsoft Outlook Express 5.00.2314.1300 X-MimeOLE: Produced By Microsoft MimeOLE V5.00.2314.1300 ----- Original Message ----- From: Bart Ogryczak To: Sent: Thursday, August 12, 1999 4:24 PM Subject: Re: [SQL] Searching Text Fields - Case Sensitive? > > 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 (and of course if foobar is a variable, it would also have to be made lower case by the program calling pgsql), or to use the pattern matching case-insensitive operator? Yours, Moray From bart@bart.w-wa.pl Fri Aug 13 09:06:54 1999 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 From tgl@sss.pgh.pa.us Fri Aug 13 14:28:18 1999 Received: from sss.sss.pgh.pa.us (sss.pgh.pa.us [209.114.166.2]) by hub.org (8.9.3/8.9.3) with ESMTP id KAA76500 for ; Fri, 13 Aug 1999 10:28:18 -0400 (EDT) (envelope-from tgl@sss.pgh.pa.us) Received: from sss.sss.pgh.pa.us (localhost [127.0.0.1]) by sss.sss.pgh.pa.us (8.9.1/8.9.1) with ESMTP id KAA04271; Fri, 13 Aug 1999 10:27:15 -0400 (EDT) To: "Moray McConnachie" cc: pgsql-sql@postgreSQL.org Subject: Re: [SQL] Searching Text Fields - Case Sensitive? In-reply-to: Your message of Fri, 13 Aug 1999 08:48:06 +0100 <002801bee560$2e499d30$760e01a3@oucs.ox.ac.uk> Date: Fri, 13 Aug 1999 10:27:15 -0400 Message-ID: <4269.934554435@sss.pgh.pa.us> From: Tom Lane "Moray McConnachie" writes: >> SELECT * FROM mydb WHERE lower(description) LIKE "%foobar%"; > Brings up an interesting question. Which is faster, to use the > lower/upper functions as above (and of course if foobar is a variable, > it would also have to be made lower case by the program calling > pgsql), or to use the pattern matching case-insensitive operator? Offhand I'd guess that the case-insensitive regex would be faster than the above, because it avoids the overhead of making an extra function call. (Functions that return text, or any other variable-length data type, require a memory allocation step --- for typical string lengths I'd imagine the malloc costs more than the actual character-slinging...) *However*, the situation changes considerably if you have an index on lower(description) and a pattern that allows the index to be used. The above pattern does not, but any left-anchored pattern, say 'foobar%', would allow index restriction clauses to be generated and used. The effective query with a left-anchored pattern is like this: ... WHERE lower(description) LIKE "foobar%" AND lower(description) >= "foobar" AND lower(description) < "foobas"; (actually there are some character-set issues that complicate matters, but that's the basic idea). When you have an index on lower(description), the system can and will use the last two clauses to restrict an indexscan so that tuples outside the range foobar to foobas are never even fetched. Obviously, this can make for a speedup of orders of magnitude, swamping any nitpicky little questions of how fast a particular expression can be evaluated. If you use a case-insensitive regex then this doesn't work, even for a left-anchored pattern ('^foobar'), because the system knows that 'f' can match either 'f' or 'F' so it can't generate the index restriction clause... regards, tom lane