ORA-12728:正则表达式中的范围无效 [英] ORA-12728: invalid range in regular expression

问题描述

我想检查表中是否插入了有效的电话号码，所以我的触发代码在这里:

 select start_index
              into mob_index
              from gmarg_mobile_operators
              where START_INDEX = substr(:new.contact_info,0,3);

              if (REGEXP_LIKE (:NEW.CONTACT_INFO,'^(?:'|| mob_index ||')[\-,\:]{0,1}[0-9][0-9][\-,\:]{0,1}[0-9][0-9][\-,\:]{0,1}[0-9][0-9]')) then
                found := 1;
              end if;

我已经检查了我的正则表达式:"^(?: 555)[-,:] {0,1} [0-9] [0-9] [-,:] {0,1} [0- 9] [0-9] [-,:] {0,1} [0-9] [0-9]"，这是正确的.

当我运行触发器时，它会成功编译，但是在插入一行时会显示以下错误:

 insert into GMARG_CONTACTS
(CLINET_ID,CONTACT_INFO,contact_type_id)
values
(0,'555194117','Mobile')
Error report -
SQL Error: ORA-12728: invalid range in regular expression
ORA-06512: at "HR.GMARG_TRIGGER_CONTACT", line 12
ORA-04088: error during execution of trigger 'HR.GMARG_TRIGGER_CONTACT'
12728. 00000 -  "invalid range in regular expression"
*Cause:    An invalid range was found in the regular expression.
*Action:   Ensure a valid range is being used.

因此，如果我的正则表达式正确，为什么oracle会显示错误? 我试图找到答案，或者重新定义我的正则表达式，但是没有前进的步骤... 预先谢谢你

解决方案

正则表达式不要使用\来保护括号表达式中的- .您只需将-作为第一个字符，放在开括号之后:

IF REGEXP_LIKE('--,,::', '[\-,:]*')
...

=> ORA-12728: invalid range in regular expression

如果您好奇，遇到Oracle时会理解:范围从\到,的任何字符或字符:" .引起异常的原因是\根据其ASCII值似乎在之后 ,.而且Oracle不接受 range 在结束值之后具有起始值.

另一方面:

 IF REGEXP_LIKE('--,,::', '[-,:]*')

按预期工作.

---

_{作为旁注，[-,:]{0,1}表示"-，,或:" 的零个或一次出现.}

I want to check if valid phone number is inserting in table, so my trigger code is here:

 select start_index
              into mob_index
              from gmarg_mobile_operators
              where START_INDEX = substr(:new.contact_info,0,3);

              if (REGEXP_LIKE (:NEW.CONTACT_INFO,'^(?:'|| mob_index ||')[\-,\:]{0,1}[0-9][0-9][\-,\:]{0,1}[0-9][0-9][\-,\:]{0,1}[0-9][0-9]')) then
                found := 1;
              end if;

I've checked my regex: "^(?:555)[-,:]{0,1}[0-9][0-9][-,:]{0,1}[0-9][0-9][-,:]{0,1}[0-9][0-9]" on several online tools and it is correct.

When I run my trigger, it compiles successfully, but during inserting a row following error is shown:

 insert into GMARG_CONTACTS
(CLINET_ID,CONTACT_INFO,contact_type_id)
values
(0,'555194117','Mobile')
Error report -
SQL Error: ORA-12728: invalid range in regular expression
ORA-06512: at "HR.GMARG_TRIGGER_CONTACT", line 12
ORA-04088: error during execution of trigger 'HR.GMARG_TRIGGER_CONTACT'
12728. 00000 -  "invalid range in regular expression"
*Cause:    An invalid range was found in the regular expression.
*Action:   Ensure a valid range is being used.

So, if my regex is correct, why does oracle shows error? I tried to find answer, or redifine my regex, but no forward steps... thank you in advance

解决方案

Regexp don't use \ to protect - in a bracket expression. You only have to put - as the first character, just after the opening bracket:

IF REGEXP_LIKE('--,,::', '[\-,:]*')
...

=> ORA-12728: invalid range in regular expression

If you're curious, when encountering [\-,:] Oracle understand: "any character in the range from \ to , or the character :". The reason why this raises an exception is \ appears to be after , according to their ASCII value. And Oracle don't accept range having a starting value after the ending one.

On the other hand:

 IF REGEXP_LIKE('--,,::', '[-,:]*')

Works as expected.

---

_{As a side note, [-,:]{0,1} meaning "zero or one occurrence of - or , or :" could be written [-,:]?.}

这篇关于ORA-12728:正则表达式中的范围无效的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！