PostgreSQL Word Border Words?

Question

Does PostgreSQL support \b ?

I try \bAB\b , but it doesn't match anything, while (\W|^)AB(\W|$) does. These two expressions are essentially the same, aren't they?

Answer 1

PostgreSQL uses \m , \m , \y and \y as word boundaries:

 \m matches only at the beginning of a word \M matches only at the end of a word \y matches only at the beginning or end of a word \Y matches only at a point that is not the beginning or end of a word 

See Conditional Expression Restrictions in the manual.

There is also [[:<:]] and [[:>:]] , which correspond to the beginning and end of a word. From the manual :

There are two special cases of bracket expressions: bracket expressions [[:<:]] and [[:>:]] are constraints that correspond to empty lines at the beginning and end of a word, respectively. A word is defined as a sequence of word characters that are not preceded or accompanied by word characters. The word character is an alnum character (as defined by ctype) or an underscore. This extension is compatible but not specified by POSIX 1003.2 and should be used with caution in software designed for portability to other systems. Typically, the preferred restrictions described below are preferred (they are not more standard, but certainly easier to print).

Answer 2

Simple example

 select * from table_name where column ~* '\yAB\y'; 

This will work in 9.1 and matche AB AB ab - text text ab text ab text-ab-text text AB text ...

But you have to be careful in 9.2. you should use:

 select * from sometable where name ~* '\\yAB\\y'; 

Pay attention to double slashes .
In 9.2, the standard_conforming_strings parameter is set to OFF by default. But you can install it manually:

 set standard_conforming_strings=on; 

Then: select * from table_name where column ~* '\yAB\y'; must work.

Answer 3

Exact search of a word in the text:

I had the following problem.

I wanted to search for all contacts that have "cto" as the exact word in the headers, but the results returned results with the name "Director" in it, I used the following query

 select * from contacts where title ilike '%cto%'; 

I also tried with whitspaces around the wildcard as "% cto%", it matched the text that contains "cto", got results like "vp, cto and manger" but not results with the exact title like "cto",

I wanted the results "vp, cto and manger" and "cto" to be obtained, but not the "director" in the results

After me they worked

 select * from contacts where title ~* '\\ycto\\y'; ~ Matches regular expression, case sensitive ~* Matches regular expression, case insensitive