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 ---------------------------(end of broadcast)--------------------------- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to majordomo@xxxxxxxxxxxxxx so that your message can get through to the mailing list cleanly
