Received: from merganser.its.uu.se (merganser.its.uu.se [130.238.6.236]) by hub.org (8.9.3/8.9.3) with ESMTP id VAA12102 for ; Sat, 3 Jun 2000 21:41:18 -0400 (EDT) (envelope-from peter@localhost.its.uu.se) Received: from regulus.student.UU.SE ([130.238.5.2]:60975 "EHLO regulus.its.uu.se") by merganser.its.uu.se with ESMTP id ; Sun, 4 Jun 2000 03:40:20 +0200 Received: from peter (helo=localhost) by regulus.its.uu.se with local-esmtp (Exim 3.02 #2) id 12yPUc-0002Tj-00; Sun, 04 Jun 2000 03:46:22 +0200 Date: Sun, 4 Jun 2000 03:46:22 +0200 (CEST) From: Peter Eisentraut To: "T.J.Farrell" cc: pgsql-sql@postgresql.org Subject: Re: LIKE vrs ~~ In-Reply-To: <000601bfcd9a$c39f06e0$2c3cfac1@oemcomputer> Message-ID: MIME-Version: 1.0 Content-Type: TEXT/PLAIN; charset=ISO-8859-1 Content-Transfer-Encoding: 8BIT X-Archive-Number: 200006/30 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%' If you're comparing ~~ with LIKE then there's no difference at all. (Only the extra cycles to convert LIKE to ~~ internally.) If you're comparing true case-insensitive matching to using UPPER, then the latter is probably faster but doesn't really do the same thing. -- Peter Eisentraut Sernanders väg 10:115 peter_e@gmx.net 75262 Uppsala http://yi.org/peter-e/ Sweden