Re: Quick Regex Question

Howard Cole wrote:

> Hmm. Still think they are crazy - sometimes the characters are interpreted
> as literals - other times not? Thats crazy in my book!

Yeah.  ^, like a lot of other chars, means different things when at the
beggining of a [] (where it means "negate the character class") than
any other position inside the [] (where it means "a literal ^") than
outside [] (where it means "anchor to beginning of string").

> I am trying to match the beginning of a name, so to search for
> 'how' in 'Howard Cole' should match
> 'col' in 'Howard Cole' should match
> 'ole' in 'Howard Cole' should NOT match,
>
> So using ~* '(^| )col' works for me! As would '(^col| col)' etc.

I think you are looking for [[:<:]] which means "beginning of word":

alvherre=# select 'Howard Cole' ~* '[[:<:]]ole';
 ?column?
----------
 f
(1 row)

alvherre=# select 'Howard Cole' ~* '[[:<:]]col';
 ?column?
----------
 t
(1 row)

I use to know the symbol as \< on other regex engines.  It is also
known as \m on Postgres.  It is not specified by the standard, so be
careful with it.  Note double backslash is needed:

alvherre=# select 'Howard Cole' ~* e'\\mcol';
 ?column?
----------
 t
(1 row)

alvherre=# select 'Howard Cole' ~* e'\\mole';
 ?column?
----------
 f
(1 row)



> Just as an aside, is there a function that escapes my search string so that
> any special regex characters are replaced? For example, if I was going to
> search for 'howard.cole' in the search string it would convert to
> 'howard[:.:]cole' or 'howard\.cole' - and then convert that into a postgres
> compatible string!

Hmm, I have no idea about that.

--
Alvaro Herrera                                http://www.CommandPrompt.com/
PostgreSQL Replication, Consulting, Custom Development, 24x7 support