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