Re: LIKE and regex

"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