Oracle Regexp在SQL中失败 [英] Oracle Regexp fails in SQL

问题描述

我正在尝试使用此regexp语句:

I am trying to use this regexp statement:

select 1 from dual where regexp_like('040', '^[\d\*]{3}$');

没有返回任何输出，但是有趣的是:

No output is returned but interesting to say:

select 1 from dual where regexp_like('040', '^[[:digit:]\*]{3}$');

有效.

为什么?

我正在使用Oracle 11.2.

I am on Oracle 11.2.

推荐答案

在第二个示例中，您正在使用POSIX字符类.在第一个示例中，您要混合使用POSIX和'Perl-受影响的扩展名.随附的匹配字符列表 [ ... ]:

In your second example you're using a POSIX character class. In your first example you're mixing POSIX and 'Perl-influenced extensions'. The matching character-list enclosed in [ ... ]:

匹配括号内列表中的任何单个字符.列表中允许使用以下运算符，但其中包含的其他元字符被视为文字...

Matches any single character in the list within the brackets. The following operators are allowed within the list, but other metacharacters included are treated as literals ...

因此在[]中，反斜杠元字符被视为文字反斜杠，您要在其中查找任何字符\，d或*，而不是为您期望的一位数字-\d的解释不像方括号中的那样.而且您的示例字符串中没有这些文字字符，因此找不到匹配项.

So within the [], the backslash metacharacter is treated as a literal backslash, which you're looking for any of the characters \, d or *, and not for a single digit as you expect - the \d just isn't interpreted like that within the brackets. And you don't have any of those literal characters in your sample string, therefore it doesn't find a match.

即使在第二个版本中，\*也只会匹配那些文字字符，因此不会添加任何内容.除非您要匹配'12*'或'1\2'这样的值，否则似乎不太可能.

Even in your second version, the \* is only going to match those literal characters too, so that isn't adding anything; unless you want to match a value like '12*' or '1\2', which seems unlikely.

所以您可能只想简单一点:

So you may just want the simpler:

select 1 from dual where regexp_like('040', '^\d{3}$');

或等价物:

select 1 from dual where regexp_like('040', '^[[:digit:]]{3}$');

这篇关于Oracle Regexp在SQL中失败的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！