Oracle REGEXP_INSTR()和"a-z"字符范围与预期不符 [英] Oracle REGEXP_INSTR() and "a-z" character range doesn't match as expected

问题描述

我想在oracle数据库中使用REGEXP_INSTR()检查小写/大写字符.我知道[:upper:]和[:lower:] POSIX字符类，但是我使用了a-z，这给了我我不明白的真正奇怪的结果.有人可以解释吗?

I want to use REGEXP_INSTR() within an oracle database to check for lower/uppercase characters. I'm aware of [:upper:] and [:lower:] POSIX character classes, but I went with a-z that gives me really weird results I don't understand. Can someone explain this?

SELECT REGEXP_INSTR('abc','[A-Z]',1,1,0,'c') FROM DUAL
-- Got 2, expected 0

SELECT REGEXP_INSTR('zyx','[A-Z]',1,1,0,'c') FROM DUAL
-- Got 1, expected 0

SELECT REGEXP_INSTR('ABC','[a-z]',1,1,0,'c') FROM DUAL
-- Got 1, expected 0

SELECT REGEXP_INSTR('ZYX','[a-z]',1,1,0,'c') FROM DUAL
-- Got 2, expected 0

SELECT REGEXP_INSTR('a3','[A-F0-9]',1,1,0,'c') FROM DUAL
-- Got 2, expected 2

SELECT REGEXP_INSTR('b3','[A-F0-9]',1,1,0,'c') FROM DUAL
-- Got 1, expected 2

SELECT REGEXP_INSTR('b3','[A-F0-9]') FROM DUAL
-- Got 1, expected 1 or 2

SELECT REGEXP_INSTR('a3','[A-F0-9]') FROM DUAL
-- Got 2, expected same as above

推荐答案

该行为的原因是排序规则.请参见 NLS_SORT文档:

The reason for the behavior is the collation rules. See the NLS_SORT documentation:

• 如果该值为BINARY，则ORDER BY查询的整理顺序基于字符的数字值(需要较少系统开销的二进制排序).
  
• 如果值是命名的语言排序，则排序基于定义的语言排序的顺序. NLS_LANGUAGE参数支持的大多数(但不是全部)语言也支持同名的语言排序.

• If the value is BINARY, then the collating sequence for ORDER BY queries is based on the numeric value of characters (a binary sort that requires less system overhead).
  
• If the value is a named linguistic sort, sorting is based on the order of the defined linguistic sort. Most (but not all) languages supported by the NLS_LANGUAGE parameter also support a linguistic sort with the same name.

将NLS_SORT设置为BINARY，以便可以按与ASCII表

Set the NLS_SORT to BINARY so that the [A-Z] could be parsed in the same order as in the ASCII table,

alter session set nls_sort = 'BINARY'

然后，您将获得一致的结果.

Then, you will get consistent results.

请参见在线演示.

这篇关于Oracle REGEXP_INSTR()和"a-z"字符范围与预期不符的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！