Postgresql中用于查找完整单词的正则表达式

Question

我希望只获取某一列中包含特定单词的记录，我尝试使用WHERE ... IN (...)语句，但在这个where子句中Postgres区分大小写。

因此，我尝试使用正则表达式和~*运算符。

以下是一个SQL代码片段，它返回数据库中的所有列和表，我想限制行，只获取正则表达式中的表。

SELECT ordinal_position as COLUMN_ID, TABLE_NAME, COLUMN_NAME
                        FROM information_schema.columns
                        WHERE table_schema = 'public' and  table_name ~* 'PRODUCTS|BALANCES|BALANCESBARCODEFORMATS|BALANCESEXPORTCATEGORIES|BALANCESEXPORTCATEGORIESSUB'
                        order by TABLE_NAME, COLUMN_ID

该正则表达式将获取BALANCES表的所有列以及包含“BALANCES”关键字的表的列。

我希望仅限制结果为完整名称。

- Menelaos Vergis

Answer 1

使用正则表达式，通常的解决方案是在当前表达式之前和之后使用单词边界。

查看无单词边界效果：http://regexr.com?35ecl 查看有单词边界效果：http://regexr.com?35eci 在PostgreSQL中，单词边界由\y表示^{（其他流行的正则表达式引擎，如PCRE、C#和Java，使用\b代替-因此在上面的正则表达式演示中使用了它-感谢@IgorRomanchenko）}。

因此，对于您的情况，可以使用下面的表达式^{（匹配与上述链接中的示例正则表达式相同）}：

'\y(PRODUCTS|BALANCES|BALANCESBARCODEFORMATS|BALANCESEXPORTCATEGORIES|BALANCESEXPORTCATEGORIESSUB)\y'

在这里查看此表达式的演示示例： http://sqlfiddle.com/#!12/9f597/1

- acdcjunior

---

在Postgres中，\b表示退格符，就像C语言一样。您可以使用\y来仅匹配单词的开头或结尾。详情请参见这里。 - Ihor Romanchenko

严谨而言，我更倾向于使用“许多其他正则表达式引擎”而不是“通常的正则表达式”。正则表达式并没有实现标准化，所以最好根据特定工具所使用的“风格”查找手册，而不要做出任何假设。 - IMSoP

@IMSoP同意。已更改该描述。PostgreSQL在使用\y方面并不孤单。正如我所使用的关于***单词边界***的链接中所指出的那样，还有一些工具也在使用它。 - acdcjunior

标记为正确答案，因为您已经疲惫不堪，并提供了所有这些有用的链接 :) - Menelaos Vergis

Answer 2

如果你想匹配整个table_name，可以使用以下方法：

'^(PRODUCTS|BALANCES|BALANCESBARCODEFORMATS|BALANCESEXPORTCATEGORIES|BALANCESEXPORTCATEGORIESSUB)$'

^匹配字符串的开头。

$匹配字符串的结尾。

详情请参考这里。

或者你也可以使用类似以下的内容：

 upper(table_name) IN ('PRODUCTS','BALANCES','BALANCESBARCODEFORMATS','BALANCESEXPORTCATEGORIES', ...)

使IN不区分大小写。

- Ihor Romanchenko