KingbaseES迁移Oracle regexp_count函数

最新推荐文章于 2024-09-17 21:18:06 发布

沉舟侧畔千帆过_

最新推荐文章于 2024-09-17 21:18:06 发布

阅读量488

点赞数

分类专栏：数据库文章标签： oracle 正则表达式数据库

原文链接：https://bbs.kingbase.com.cn/thread-2303-1-1.html?_dsign=25badbeb

版权

数据库专栏收录该内容

550 篇文章 31 订阅

订阅专栏

1. 问题

2. 文档概述

3. Oracle regexp_count()函数功能调研

3.4.2. 示例2：使用position参数，指定搜索的起始下标

3.4.3. 示例3：使用正则表达式匹配a-z任意单个字符

3.4.4. 示例4：从给定的模式中匹配字符与数字结合的字符串

3.4.5. 示例5：使用匹配行为‘i’、‘c’不区分大小写和区分大小写

3.4.6. 示例6：使用匹配行为‘x’忽略空格

3.4.7. 示例7：使用匹配行为‘n’

3.4.8. 示例8：自动忽略目标字符串中的括号表达式

3.4.9. 示例9：使用匹配行为‘m’

4. 基于KingbaseES适配regexp_count函数功能的实现

4.1. 寻找一个已有的和regexp_count函数功能相似的现成函数

4.2. regexp_count函数实现代码

1. 问题

KingbaseES如何迁移regexp_count函数功能。

2. 文档概述

本文主要是通过调研Oracle的regexp_count函数功能，然后基于KingbaseES自定义函数形式实现regexp_count函数功能。

3. Oracle regexp_count()函数功能调研

3.1. 函数名称

REGEXP_COUNT ( source_char, pattern , position , match_param)

3.2. 函数功能

返回目标字符串pattern（也可以使用正则表达式），在源字符串source_char中出现的次数。

返回值若等于0，则表示未找到匹配项。

3.3. 参数介绍

3.3.1. source_char

需要用来分析的源字符串，可以是CHAR，VARCHAR2, NCHAR, NVARCHAR2, CLOB或NCLOB中的任意类型。

3.3.2. pattern

正则表达式，通常是一个文本文字。可以是CHAR，VARCHAR2, NCHAR, NVARCHAR2, CLOB或NCLOB中的任意类型。最大长度512个字节。

如果pattern和source_char的数据类型不一致会自动转换同source_char数据类型。

regexp_count函数会自动忽略pattern中的括号表达式，例如‘(123(45))’等价于‘12345’。

3.3.3. position

position是一个正整数它表示从源字符串source_char的哪个位置开始分析，可以省略，默认是1，即source_char的第一个位置。

3.3.4. match_param

表示regexp_count的默认匹配行为，可以包含以下一个或多个字符：

‘i’：指定不区分大小写的匹配；
‘c’：指定区分大小写的匹配，默认区分大小写；
‘n’：允许局点(.)作为通配符去匹配换行符。如果省略该参数，则默认句点将不匹配换行符；
‘m’：将源字符串视为多行。即Oracle将插入符号(^)和美元符号($)分别看做源字符串中任意位置任何行的开始和结束，而不仅仅是看作整个源字符串的开始或结束。如果省略该参数，则默认将源字符串看作一行；
‘x’：忽略空格字符。默认情况下，空格字符与自身相匹配。

如果match_param包含多个相互矛盾的字符，则Oracle使用最后一个字符。例如指定match_param为‘ic’，则Oracle最终选取‘c’匹配行为，即使用区分大小写。

如果省略match_param，则：

默认大小写由regexp_count函数的确定排序规则确定；
句点(.)与换行符不匹配；
源字符串被视为单行。

3.4. 函数示例

3.4.1. 示例1：常规示例

SQL> select REGEXP_COUNT('welcome','w') from DUAL;

REGEXP_COUNT('WELCOME','W')
----------------------------------------------------
            1

3.4.2. 示例2：使用position参数，指定搜索的起始下标

SQL> select REGEXP_COUNT('welcome','e') , REGEXP_COUNT('welcome','e',3 ) from DUAL;

REGEXP_COUNT('WELCOME','E') REGEXP_COUNT('WELCOME','E',3)
--------------------------- -------------------------------------------------------------------------------
2                             1

3.4.3. 示例3：使用正则表达式匹配a-z任意单个字符

SQL> select regexp_count('abc123', '[a-z]'), regexp_count('a1b2c3', '[a-z]') from dual;

REGEXP_COUNT('ABC123','[A-Z]') REGEXP_COUNT('A1B2C3','[A-Z]')
------------------------------ --------------------------------------------------------------------------------
      3                              3

3.4.4. 示例4：从给定的模式中匹配字符与数字结合的字符串

SQL> select regexp_count('abc123', '[a-z][0-9]'), regexp_count('a1b2c3', '[a-z][0-9]') from dual;

REGEXP_COUNT('ABC123','[A-Z][0-9]') REGEXP_COUNT('A1B2C3','[A-Z][0-9]')
-------------------------------------------------------------------------------------------------------------
         1                                   3

3.4.5. 示例5：使用匹配行为‘i’、‘c’不区分大小写和区分大小写

SQL> select REGEXP_COUNT('wElcome','e',1,'i') , REGEXP_COUNT('wElcome','e',1,'c' ) from DUAL;

REGEXP_COUNT('WELCOME','E',1,'I') REGEXP_COUNT('WELCOME','E',1,'C')
--------------------------------- -----------------------------------------------------------------------------------
         2                                 1

3.4.6. 示例6：使用匹配行为‘x’忽略空格

SQL>  select regexp_count('welcome','co m',1), regexp_count('welcome','co m', 1, 'x') from dual;

REGEXP_COUNT('WELCOME','COM',1) REGEXP_COUNT('WELCOME','COM',1,'X')
------------------------------- -------------------------------------------------------------------------------------
        0                                   1

3.4.7. 示例7：使用匹配行为‘n’

SQL> select regexp_count('welcomxe','com.e',1,'n'), regexp_count('welcomxe','com.e',1) from dual;

REGEXP_COUNT('WELCOMXE','COM.E',1,'N') REGEXP_COUNT('WELCOMXE','COM.E',1)
-------------------------------------- ----------------------------------
         1                                  1

句点(.)可匹配任何字符，不管是否使用匹配行为‘n’。

SQL> select regexp_count('welco
  2  me','co.m',1,'n') from dual;

REGEXP_COUNT('WELCOME','CO.M',1,'N')
-------------------------------------------------------------
              1

SQL> select regexp_count('welco
  2  me','co.m',1) from dual;

REGEXP_COUNT('WELCOME','CO.M',1)
---------------------------------------------------------
        0

但是如果不使用匹配行为‘n’则句点(.)不匹配换行符。

3.4.8. 示例8：自动忽略目标字符串中的括号表达式

SQL> select regexp_count('welcome','c(om)',1),regexp_count('welcome','com',1) from dual;

REGEXP_COUNT('WELCOME','C(OM)',1) REGEXP_COUNT('WELCOME','COM',1)
--------------------------------- ---------------------------------------------------------------------------------
       1                               1

3.4.9. 示例9：使用匹配行为‘m’

SQL> select regexp_count('
  2  welcome
  3  hello
  4  world','llo$',1,'m') from dual;

REGEXP_COUNT('WELCOMEHELLOWORLD','LLO$',1,'M')
------------------------------------------------------------------------------------------------
             1

SQL> select regexp_count('
  2  welcome
  3  hello
  4  world','llo$',1) from dual;

REGEXP_COUNT('WELCOMEHELLOWORLD','LLO$',1)
--------------------------------------------------------------------------------------------
             0

SQL> select regexp_count('
  2  welcome
  3  hello
  4  world','^worl',1) from dual;

REGEXP_COUNT('WELCOMEHELLOWORLD','^WORL',1)
---------------------------------------------------------------------------------------------
        0

SQL> select regexp_count('
  2  welcome
  3  hello
  4  world','^worl',1,'m') from dual;

REGEXP_COUNT('WELCOMEHELLOWORLD','^WORL',1,'M')
-----------------------------------------------
                     1

使用匹配模式‘m’，则表示匹配任意行的开头和结尾。其中符号(^)表示任意行开头，符号($)表示任意行结尾。

4. 基于KingbaseES适配regexp_count函数功能的实现

要想通过自定义函数实现Oracle的regexp_couont函数，那么大概就有两种方案：

方案一，从头开始实现regexp_count的全部功能；

方案二，寻找一个功能接近regexp_count的现成函数，然后进行优化从而达到预期的regexp_count函数功能；

很明显，方案二的实现更容易一些，所以本文采取方案二的思路。

4.1. 寻找一个已有的和regexp_count函数功能相似的现成函数

经过分析，发现KingbaseES的函数regexp_matches和Oracle的regexp_count函数功能相似。

具体功能对比：

功能	Oracle	KingbaseES
指定搜索起始位置	支持	不支持
使用正则表达式	支持	支持
区分大小写	支持	支持
忽略空格	支持	支持
忽略括号	支持	支持
匹配换行	支持	支持，但是行为相反，默认匹配换行符
匹配起始符号和结束符号	支持	支持

基于上述的分析，我们需要逐一实现V8R3/V8R6不支持项以及和Oracle功能不匹配的项：

实现“指定搜索起始位置”功能；
实现“匹配换行”功能；
参数限制；

4.2. regexp_count函数实现代码

CREATE OR REPLACE FUNCTION regexp_count(TEXT, TEXT, INT DEFAULT 1, TEXT DEFAULT '') RETURNS BIGINT AS $$
DECLARE
	source_char text := NULL;
	source_cha_length int := char_length($1);
	count BIGINT := 0;
	action text := '';
	n_flag INT := 0;
	param text := NULL;
	location int := 0;
	start_end_flag INT := 0;
	BEGIN
		WHILE location < char_length($4) LOOP
			param := substring($4 from location + 1 for 1);
			IF param = 'i' OR param = 'c' OR param = 'n' OR param = 'm' OR param = 'x' THEN
				IF param <> 'n' THEN
					action := action || param;
				ELSE
					n_flag := 1;
				END IF;
			ELSE
				raise 'invalid regexp option:%', param;
			END IF;		
			
			location := location + 1;
		END LOOP;
		
		IF substring($2 from 1 for 1) = '^' OR substring($2 from char_length($2) for 1) = '$' THEN
			IF n_flag = 1 THEN
				action := action || 'ng';
			ELSE
				action := action || 'g';
			END IF;
		ELSE
			IF n_flag = 0 THEN
				action := action || 'ng';
			ELSE
				action := action || 'g';
			END IF;
		
		END IF;
				
		IF $3 <=0 THEN	
			raise 'param position % is out of range', $3;
		ELSIF $3 = 1 THEN
			count := (select count(m) from regexp_matches($1, $2, action) m);
			return count;
		ELSE
			source_char = substring($1 from $3 for source_cha_length);
			count := (select count(m) from regexp_matches(source_char, $2, action) m);
			return count;
		END IF;	
		
	END;
$$LANGUAGE PLPGSQL;