Oracle中使用regexp_like对中文字符进行匹配以及不起作用原因分析

在使用regexp_like函数对数据库中表记录的中文字段进行正则匹配的过程中，出现了不起作用的情况。

一、背景

表记录如下：

我想筛选出以德州分公司.开头，以分公司结尾的记录，也就是期待的输出如下：

二、尝试

尝试了两种方法结果都没有输出：

方法1

此方法理论上可行，但是在我的会话窗口没有期待的输出，原因见下面的分析。

select *
    FROM SF_GROUP T
   where regexp_like(t.group_name,
                     '^德州分公司\.([' || unistr('\4e00') || '-' ||
                     unistr('\9fa5') || ']*)公司$');

方法2

此方法是没法直接在oracle里执行的，具体原因见下面的原因分析。

 select *
    FROM SF_GROUP T
   where regexp_like(t.group_name,
                     '^德州分公司\.([\u4e00-\u9fa5]*)公司$');

三、解决办法

对于没有期待结果输出的情况，我在stackoverflow提了个问题，感谢@MT0、@Alex Poole、@HRK的回答，目前整理出了几种解决方法。

方法1

下面有开始我用此方法不起作用的原因分析。

select *
    FROM SF_GROUP T
   where regexp_like(t.group_name,
                     '^德州分公司\.([' || unistr('\4e00') || '-' ||
                     unistr('\9fa5') || ']*)公司$');

方法2

下面有此方法为什么不可以的原因分析。

SELECT *
  FROM sf_group
 WHERE REGEXP_LIKE(group_name, '^德州分公司\.[一-龥]*公司$')

方法3

SELECT * FROM sf_group WHERE group_name LIKE '德州分公司.%公司';

方法4

select *
  FROM SF_GROUP T
 where regexp_like(t.group_name, '^德州分公司.(.*)公司$');

四、原因分析

不可行情形1

 select *
    FROM SF_GROUP T
   where regexp_like(t.group_name,
                     '^德州分公司\.([\u4e00-\u9fa5]*)公司$');

上面的SQL不可行的原因：oracle里是使用字节值来计算字符的，不能使用 ‘\xxxx’ 形式的 Unicode 十六进制编码值。对于Unicode十六进制编码应该使用unistr('\4e00') and unistr('\9fa5')来替代。

更多细节官方说明： Regular Expression Operator Multilingual Enhancements documentation：

Oracle lets you enter multibyte characters directly, if you have a direct input method, or you can use functions to compose the multibyte characters. You cannot use the Unicode hexadecimal encoding value of the form ‘\xxxx’. Oracle evaluates the characters based on the byte values used to encode the character, not the graphical representation of the character. All accented characters are considered word characters.

不可行情形2

select *
    FROM SF_GROUP T
   where regexp_like(t.group_name,
                     '^德州分公司\.([' || unistr('\4e00') || '-' ||
                     unistr('\9fa5') || ']*)公司$');

以及

SELECT *
  FROM sf_group
 WHERE REGEXP_LIKE(group_name, '^德州分公司\.[一-龥]*公司$')

上面的两个SQL语法上是没问题的，下面的就是第一个的计算值之后的硬编码，但是在我这里却没有正确输出，原因如下：

我的NLS会话设置影响了结果的输出。

而REGEXP_LIKE对排序敏感(官网说明)，不同的排序方式可能会影响结果输出。

我修改会话的排序设置后就正常输出了：

 alter session set nls_comp = 'LINGUISTIC';

 alter session set nls_sort = 'SCHINESE_RADICAL_M';

参考文章：

1. How to use regexp_like in oracle?；

2. Pattern-matching Conditions；

3. Linguistic Sorting and Matching；

4. https://www.jianshu.com/p/4227a9f71a72;