概要描述

本文主要介绍，在inceptor中，如何实现oracle的regexp_substr和connect by level获取拆分字符。

首先会从oracle的角度解析下这个用法是如何逐步实现的，进而展开到inceptor的改写方法。

详细说明

一、oracle中的实现

REGEXP_SUBSTR 函数格式如下：
    function REGEXP_SUBSTR(String, pattern, position, occurrence, modifier)
    __srcstr     ：需要进行正则处理的字符串
    __pattern    ：进行匹配的正则表达式
    __position   ：起始位置，从第几个字符开始正则表达式匹配（默认为1）
    __occurrence ：标识第几个匹配组，默认为1
    __modifier   ：模式（'i'不区分大小写进行检索；'c'区分大小写进行检索。默认为'c'。）

实际应用如下：在oracle中，使用一条语句实现将’34,56,-23’拆分成’34’,’56’,’-23’的集合。

--1、查询使用正则分割后的第一个值，也就是34
> SELECT REGEXP_SUBSTR('34,56,-23','[^,]+',1,1,'i') AS STR FROM DUAL;
--结果是：34

--2、查询使用正则分割后的最后一个值，也就是-23
> SELECT REGEXP_SUBSTR('34,56,-23','[^,]+',1,3,'i') AS STR FROM DUAL;
--结果是：-23

--3、获取一个多个数值的列，从而能够让结果以多行的形式展示出来
> SELECT LEVEL FROM DUAL CONNECT BY LEVEL <=7;
--结果是：
--1
--2
--3
--4
--5
--6
--7

--4、将上面REGEXP_SUBSTR的occurrence关联
> SELECT NVL(REGEXP_SUBSTR('34,56,-23', '[^,]+', 1, LEVEL, 'i'), 'NULLL') AS STR FROM DUAL CONNECT BY LEVEL <= 7;
--结果是：
--34
--56
---23
--null
--null
--mull
--null

--5、优化上面的SQL语句，让生成的行的数量符合实际情况
> SELECT REGEXP_SUBSTR('34,56,-23', '[^,]+', 1, LEVEL, 'i') AS STR FROM DUAL \
> CONNECT BY LEVEL <= LENGTH('34,56,-23') - LENGTH(REGEXP_REPLACE('34,56,-23', ',', '')) + 1;
--结果是：
--34
--56
---23

二、inceptor中的实现

REGEX_SUBSTR 函数格式如下：
    function regex_substr (,  [, [,  [,  ] ]])

    # 该函数需要至少两个函数：一段文本 和一个正则表达式 。
    # 该函数会将 中和  第一个发生匹配的子字符串返回。
    #  是一个正整数，用于指定从 中的第几个字符开始匹配。 
    # 是一个正整数，用于指定返回第几个发生匹配的子字符串。
    #  用于指定匹配方式，
        i：大小写不敏感；
        c：大小写敏感；
        n 将 .（通配符）和换行符匹配，默认为不匹配；
        m 将  作为多行来处理，将 ^ 作为行开头，$作为行结尾； 
        x 忽略空格。

注意：inceptor中是regex_substr，oracle中是regexp_substr，两者差了一个字母p

先说结论，改写方式如下：

SELECT
    REGEX_SUBSTR('34,56,-23', '[^,]+', 1, pos + 1, 'i') AS STR
FROM
    system.dual
    lateral view posexplode(split('34,56,-23', ',')) t AS
    pos, val;

在高版本下，该语句可以正常执行，但在某些低版本下，需要 联系研发换包（参考 SLA-10226） 优先解决position的问题。
参考 WARP-50953。原因是oracle默认position是从1开始的, 而inceptor是从0开始的。
验证sql，结果为第一个字符串34，则正常；为56，则失败。：

SELECT REGEX_SUBSTR('34,56,-23', '[^,]+', 1, 1, 'i') AS STR  FROM system.dual;

附：connect by level的改写

--oracle:
SELECT LEVEL LV FROM DUAL CONNECT BY LEVEL <= 100

--方案一：
SELECT generate_series(1,10);

--方案二：
SET character.literal.as.string=TRUE;
SELECT
    t.pos+1
FROM
    system.dual
    lateral view posexplode(split(space(100), '')) t AS
    pos,val;