Re: LIKE and regex

"Mitch Vincent" <mitch@venux.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