Re: LIKE and regex

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: "Mitch Vincent" <mitch(at)venux(dot)net>
Cc: pgsql-sql(at)postgresql(dot)org
Subject: Re: LIKE and regex
Date: 2000-05-18 16:35:17
Views: Raw Message | Whole Thread | Download mbox | Resend email
Lists: pgsql-sql

"Mitch Vincent" <mitch(at)venux(dot)net> 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

In response to


Browse pgsql-sql by date

  From Date Subject
Next Message Kyle Bateman 2000-05-18 16:53:51 Pre-toast toast to toast
Previous Message Alex Guryanow 2000-05-18 16:22:01 indecies are not used by '<=' operator on varchar fields