Received: from sss2.sss.pgh.pa.us (sss.pgh.pa.us [209.114.166.2]) by hub.org (8.9.3/8.9.3) with ESMTP id PAA96591 for ; Sun, 4 Jun 2000 15:19:14 -0400 (EDT) (envelope-from tgl@sss.pgh.pa.us) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss2.sss.pgh.pa.us (8.9.3/8.9.3) with ESMTP id PAA06564; Sun, 4 Jun 2000 15:19:11 -0400 (EDT) To: "T.J.Farrell" cc: pgsql-sql@postgresql.org Subject: Re: LIKE vrs ~~ In-reply-to: <000601bfcd9a$c39f06e0$2c3cfac1@oemcomputer> References: <000601bfcd9a$c39f06e0$2c3cfac1@oemcomputer> Comments: In-reply-to "T.J.Farrell" message dated "Sat, 03 Jun 2000 22:31:07 +0200" Date: Sun, 04 Jun 2000 15:19:11 -0400 Message-ID: <6560.960146351@sss.pgh.pa.us> From: Tom Lane X-Archive-Number: 200006/31 "T.J.Farrell" writes: > I was wondering about the performance incidence of : > SELECT * FROM table1 WHERE UPPER(field1) LIKE > UPPER('%thomas%'); > versus: > SELECT * FROM table1 WHERE field1 ~~ '%thomas%' Of course "~~" is just an alternate spelling of LIKE, and is case-sensitive, so the above two queries are not equivalent. Perhaps you meant to refer to "~*" which is a case-insensitive regex match ... but then you'd need a different pattern. Anyway, if you keep an index on upper(field1) then the first form is the way to go, since the system can use a left-anchored pattern as an index range restriction. regards, tom lane