PostgreSQL 正则表达式字边界？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/3825676/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
PostgreSQL Regex Word Boundaries?
提问by mpen
Does PostgreSQL support \b?
PostgreSQL 支持\b吗？
I'm trying \bAB\bbut it doesn't match anything, whereas (\W|^)AB(\W|$)does. These 2 expressions are essentially the same, aren't they?
我正在尝试，\bAB\b但它不匹配任何东西，而(\W|^)AB(\W|$)确实如此。这两个表达本质上是一样的，不是吗？
回答by Daniel Vandersluis
PostgreSQL uses \m, \M, \yand \Yas word boundaries:
PostgreSQL 使用\m, \M,\y和\Y作为单词边界：
\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 Regular Expression Constraint Escapesin the manual.
请参阅手册中的正则表达式约束转义。
There is also [[:<:]]and [[:>:]], which match the beginning and end of a word. From the manual:
还有[[:<:]]and [[:>:]]，匹配单词的开头和结尾。从手册：
There are two special cases of bracket expressions: the bracket expressions
[[:<:]]and[[:>:]]are constraints, matching empty strings at the beginning and end of a word respectively. A word is defined as a sequence of word characters that is neither preceded nor followed by word characters. A word character is an alnum character (as defined by ctype) or an underscore. This is an extension, compatible with but not specified by POSIX 1003.2, and should be used with caution in software intended to be portable to other systems. The constraint escapes described below are usually preferable (they are no more standard, but are certainly easier to type).
括号表达式有两种特殊情况：括号表达式
[[:<:]]和[[:>:]]约束条件，分别匹配词首和词尾的空字符串。单词被定义为一系列单词字符，前面和后面都不跟单词字符。单词字符是 alnum 字符（由 ctype 定义）或下划线。这是一个扩展，与 POSIX 1003.2 兼容但未指定，在旨在移植到其他系统的软件中应谨慎使用。下面描述的约束转义通常更可取（它们不再是标准的，但肯定更容易输入）。
回答by MD. Mohiuddin Ahmed
A simple example
一个简单的例子
select * from table_name where column ~* '\yAB\y';
This will match ABabab - texttext abtext ABtext-ab-texttext AB text...
这将匹配ABabab - texttext abtext ABtext-ab-texttext AB text...
But you have to use:
但你必须使用：
select * from sometable where name ~* '\yAB\y';
in case you have standard_conforming_stringsflag
set to OFF. Note the double slashes.
You can set it manually :
如果您将standard_conforming_strings标志设置为OFF. 注意双斜线。
您可以手动设置：
set standard_conforming_strings=on;
Then :select * from table_name where column ~* '\yAB\y';should work.
然后：select * from table_name where column ~* '\yAB\y';应该工作。
回答by Pramod Shinde
Exact word search in text:
文本中的精确单词搜索：
I was facing following problem.
我面临以下问题。
I wanted to search all contacts which has 'cto' as exact word in titles, but in results was getting results with title having 'director' in it, I was using following query
我想搜索标题中包含“cto”作为确切词的所有联系人，但在结果中得到的结果是标题中包含“director”的结果，我使用了以下查询
select * from contacts where title ilike '%cto%';
I also tried with whitspaces around wildcard as '% cto %', it was getting matched with text which contains ' cto ', got results like 'vp, cto and manger', but not results with exact title as 'cto'.
我还尝试在通配符周围使用空格作为“% cto %”，它与包含“ cto ”的文本匹配，得到像“vp、cto 和 manger”这样的结果，但没有准确标题为“cto”的结果。
I wanted both 'vp, cto and manger' and 'cto' in results, but not 'director' in results
我想要结果中的“副总裁、首席技术官和经理”和“首席技术官”，而不是结果中的“导演”
Following worked for me
以下对我来说有效
select * from contacts where title ~* '\ycto\y';
~ Matches regular expression, case sensitive
~* Matches regular expression, case insensitive
