PostgreSQL LIKE 子句中的正则表达式

Question

我被一个简单的正则表达式难住了。不确定自己缺少什么。我的正则表达式技能有些生疏。

我想匹配的表达式是：

select * from table where value like '00[1-9]%'
-- (third character should not be 0)

所以这应该匹配'0090D0DF143A'（格式：文本），但它并没有！

- borarak

---

SQL的LIKE操作符不支持正则表达式，需要使用similar to或~操作符：http://www.postgresql.org/docs/current/static/functions-matching.html#FUNCTIONS-POSIX-REGEXP - user330315

Answer 1

像@a_horse评论的那样，您需要使用正则表达式运算符~来使用方括号表达式。
但是还有更多。我建议：

SELECT *
FROM   tbl
WHERE  value ~ '^00[^0]'

^ ... 匹配字符串开头（原始表达式可以匹配任何位置）。
[^0] ... 表示一个方括号表达式（字符类），匹配非0的任意字符。

或者更好的方式是：

SELECT *
FROM   tbl
WHERE  value LIKE '00%'       -- starting with '00'
AND    value NOT LIKE '000%'  -- third character is not '0'

为什么？ LIKE 没有正则表达式强大，但通常比正则表达式更快。使用廉价的 LIKE 表达式可能会更快地缩小候选集合。

一般情况下，您可以使用 NOT LIKE '__0'，但由于我们已经在另一个谓词中使用了 LIKE '00%'，因此我们可以使用更窄（更便宜）的模式 NOT LIKE '000'。

Postgres 可以为左锚定表达式 value LIKE '00%'（对于大表格很重要）使用简单的 btree 索引，而对于更复杂的正则表达式可能不起作用。Postgres 的最新版本可以使用简单的正则表达式来建立索引，因此它可能适用于这个例子。详情请参见：

• Postgres 中 LIKE 和 ~ 的区别

- Erwin Brandstetter

---

也许在第一个查询中使用 '^00[^0]' 或 '^00[^0].*' 而不是 '^00[^0]%'？否则它将无法匹配示例 0090D0DF143A。 - Ihor Romanchenko

它确实使用索引来处理这个正则表达式，请参见：http://sqlfiddle.com/#!15/7019f/1 - Nick Barnes

@NickBarnes：在这种情况下，第一个查询与第二个查询的速度几乎相同。如果没有匹配索引，第二个查询将更快。我在您的fiddle上进行了测试：http://sqlfiddle.com/#!15/d9b43/5 sqlfiddle上的性能测试并不总是可靠的。我在本地9.3设置中运行了测试。 - Erwin Brandstetter

@Erwin 哇，差别还真大！我得记住这个 ;) - Nick Barnes

Answer 2

PostgreSQL的LIKE运算符不支持[charlist]，但是SIMILAR TO支持。

查看这里以获取跨数据库的全面列表。

- sunbabaphu

---

这不是特定于Postgres的。SQL标准没有为LIKE运算符定义任何正则表达式。 - user330315

永远不要使用SIMILAR TO。 - Erwin Brandstetter

为什么这样@ErwinBrandstetter？ - borarak

@borarak：我链接了我的理由。 - Erwin Brandstetter