Postgres正则表达式问题 [英] Postgres regex issue

问题描述

我需要找到存储在postgres中的所有记录，这些记录与以下正则表达式匹配：

  ^（（8 | \ + 7）[\-]？）？（\（？\d {3} \）？[\-]？）？[\d\-] {7,10} $ 
  

类似这样的东西：

  SELECT * FROM users在哪里users.phone〜'^（（8 | \ + 7）[\-]？）？（\（？\d {3} \）？[\ -]？）？[\d\-] {7,10} $'
  

但是这个错误：

无效的正则表达式：限定符操作数无效

为什么Postgres不能使用此正则表达式？

在纯Ruby中使用相同的代码就可以了。

UPDATE

问题仅在WHERE中存在。当我尝试执行以下操作时：

  SELECT'[phone]'〜'^（（8 | \ + 7）[\- ]？）（\（？\d {3} \）？[\-]？）[\d\-] {7,10}'
  

Postgres返回true。但是，当我尝试执行以下操作时：

  SELECT *来自用户WHERE users.phone〜'^（（8 | \ + 7）[ \-]？）（\（？\d {3} \）？[\-]？）[\d\-] {7,10}'
  

结果：无效的正则表达式：限定符操作数无效 。

解决方案

当您将-放在第一个或最后一个位置时，无需在字符类内进行转义，因为它不能以这种方式误读为范围：

[\-] -> [-]

[\d\-] -> [\ d-]

结束时的上限 10

在末尾添加 $ 以禁止尾随字符。

或 \D 不允许尾随位数字（但必须输入非数字）。

或（$ | \D）可以在该处结束字符串或跟随一个非数字跟随。

放在一起：

  SELECT'[phone]'〜'^（8 | \ + 7）[-]？（\（？\d {3} \）？[-]？）[\d-] {7,10}（$ | \D）'
  

否则，您的表达式就可以了，并且在PostgreSQL 9.1上对我有用。 4。无论是在 WHERE 子句中还是在 SELECT 列表中使用它，都不会有任何区别-除非您是

---

如果我在字符串字面值前加上了一个旧版本的错误（如注释中建议的@kgrittn）。使用 E ，我可以引起您收到的错误消息。该不能解释您的问题，因为您表示该表达式可以作为 SELECT 项正常工作。

但是，正如夏洛克·福尔摩斯（Sherlock Holmes）所引述的那样，当您排除了不可能的事物时，无论多么残酷的遗留物，一定是事实。

也许您只跑了一个使用 standard_conforming_strings = on ，另一个带有 standard_conforming_strings = off -这是9.1之前的旧版本中字符串文字的默认解释。也许有两个不同的客户端（对此有不同的设置）。

在使用C样式转义符的字符串常量。

I need to find all records stored in postgres, which matching following regexp:

^((8|\+7)[\- ]?)?(\(?\d{3}\)?[\- ]?)?[\d\- ]{7,10}$

Something like this:

SELECT * FROM users WHERE users.phone ~ '^((8|\+7)[\- ]?)?(\(?\d{3}\)?[\- ]?)?[\d\- ]{7,10}$'

But this one falls with error:

invalid regular expression: quantifier operand invalid

Why won't Postgres work with this regex?

Using the same one in plain Ruby works just fine.

UPDATE

Problem is only with WHERE. When i try to:

SELECT '[phone]' ~ '^((8|\+7)[\- ]?)(\(?\d{3}\)?[\- ]?)[\d\- ]{7,10}'

Postgres returns true. But when i try:

SELECT * FROM users WHERE users.phone ~ '^((8|\+7)[\- ]?)(\(?\d{3}\)?[\- ]?)[\d\- ]{7,10}'

Result: "invalid regular expression: quantifier operand invalid".

解决方案

You don't need to escape - inside character classes when you put it at the first or last position, because it cannot be misread as range that way:

[\- ] -> [- ]
[\d\- ] -> [\d -]

The way you have it the upper bound 10 at the end is futile.
Add $ at the end to disallow trailing characters.
Or \D to disallow trailing digits (but require a non-digit).
Or ($|\D) to either end the string there or have a non-digit follow.

Put together:

SELECT '[phone]' ~ '^(8|\+7)[ -]?(\(?\d{3}\)?[ -]?)[\d -]{7,10}($|\D)'

Otherwise your expression is just fine and it works for me on PostgreSQL 9.1.4. It should not make any difference whatsoever whether you use it in a WHERE clause or in a SELECT list - unless you are running into a bug with some old version (like @kgrittn suggested in the comments).

---

If I prepend the string literal with E, I can provoke the error message that you get. This cannot explain your problem, because you stated that the expression works fine as SELECT item.

But, as Sherlock Holmes is quoted, "when you have excluded the impossible, whatever remains, however improbable, must be the truth."

Maybe you ran one test with standard_conforming_strings = on and the other one with standard_conforming_strings = off - this was the default interpretation of string literals in older versions before 9.1. Maybe with two different clients (that have a different setting as to that).

Read more in the chapter String Constants with C-style Escapes in the manual.

这篇关于Postgres正则表达式问题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！