Re: Using regular expressions in LIKE

> -----Original Message-----
> From: pgsql-general-owner@postgresql.org [mailto:pgsql-general-
> owner@postgresql.org] On Behalf Of Patrick Welche
> Sent: 15 January 2004 12:09
> To: Nick Barr
> Cc: csegyud@vnet.hu; terry@ashtonwoodshomes.com; 'Pgsql-
> General@Postgresql.Org (E-mail)'
> Subject: Re: [GENERAL] Using regular expressions in LIKE
>
> On Thu, Jan 15, 2004 at 09:05:35AM -0000, Nick Barr wrote:
> > If the ^ was not there then it could theoretically match anywhere in
the
> > string. In this particular case the regular expression will probably
> > match at the beginning of the string anyway, so it is not really
> > necessary. I prefer to put that sort of thing in to make it clear to
the
> > programmer what is going on.
>
> Isn't there also a performance benefit as you can use an index if you
> say "this definitely starts at the beginning" with the '^'?

That is what I was thinking, which is the other reason why I put it in.
This is certainly the case with queries that use the LIKE operator, for
example:

where adate like 'random%';

This is only the case with the default locale I believe. I have no idea
when it comes to regexs though and specifically the ~ operator. Could
someone more knowledgeable about this stuff reply?


Nick