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 MAA29285 for ; Thu, 18 May 2000 12:35:19 -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 MAA24109; Thu, 18 May 2000 12:35:17 -0400 (EDT) To: "Mitch Vincent" cc: pgsql-sql@postgresql.org Subject: Re: LIKE and regex In-reply-to: <014b01bfc0e0$9fb352c0$4100000a@venux.net> References: <014b01bfc0e0$9fb352c0$4100000a@venux.net> Comments: In-reply-to "Mitch Vincent" message dated "Thu, 18 May 2000 11:49:17 -0400" Date: Thu, 18 May 2000 12:35:17 -0400 Message-ID: <24106.958667717@sss.pgh.pa.us> From: Tom Lane X-Archive-Number: 200005/58 "Mitch Vincent" writes: > I'm wondering if LIKE uses regex internally. No, it's just a pretty brain-dead scanner. You can find the code in backend/utils/adt/like.c if you care. Perhaps it'd make sense to translate the LIKE pattern into an equivalent regex and then use the regex code, but no one's done it. > I'm trying to find the > quickest / fastest way to search for any occurance of a given string in a > stored field. > query: select * from applicants where firstname LIKE '%mitch%'; > query: select * from applicants where firstname ~ lower('mitch'); I think the actual operator processing is going to be swamped by tuple-fetching overhead, so there's not going to be much difference. What you ought to be thinking about is how you could replace the brute-force scan with an index lookup. Possibly you could adapt the ideas in contrib/fulltextindex. regards, tom lane