regexp_split_to_table函数

1.函数介绍
使用POSIX正则（待确认）表达式pattern作为分隔符分割字符串，将分割后的一个或多个字符串展开成一张表的多行形式。
 

常见的正则表达式标准有两种
一、PCRE标准 
二、POSIX标准
其中POSIX标准又包含
1. 基本正则表达式（BRE）
            例如：
            .：匹配任意单个字符（除换行符）。
    ^：匹配行首。
            $：匹配行尾。
            *：匹配前一个字符的零次或多次（需转义为*，因为*在BRE中默认是普通字符）。
            [ ]：匹配字符组中的任意一个字符。
            \：转义字符，用于将特殊字符转为普通字符，或启用特殊功能（如$ $分组）。
2. 扩展正则表达式（ERE）
            例如：
            +：匹配前一个字符的一次或多次。
            ?：匹配前一个字符的零次或一次。
            |：逻辑或（匹配左侧或右侧表达式）。
    {n,m}：匹配前一个字符至少n次，最多m次。
            ( )：分组（无需转义），引用时用\1, \2等。
            \b：匹配单词边界（部分实现支持）。
        BRE中多数元字符需转义 例如 \|，而ERE中直接使用 例如 | 

2.使用方法
regexp_split_to_table(string text,pattern text[, flags text])
参数说明：红色会报错，可能不支持(待确认)

3.函数效果
select foo from regexp_split_to_table('the quick brown fox jumps over the lazy dog','\\s+') as foo ;
+------------+
| foo        |
+------------+
| the        |
| quick      |
| brown      |
| fox        |
| jumps      |
| over       |
| the        |
| lazy       |
| dog        | 
+------------+
9 row in set (Elapsed: 00:00:00.01)


flag=b 不支持
gbase> select foo from regexp_split_to_table('the quick brown fox jumps over the lazy dog','O','b') as foo ;
ERROR 1708 (HY000): [192.168.61.100:5050](GBA-02AD-0005)Failed to query in gnode:
DETAIL: Incorrect parameters in the call to native function 'regex_split_to_table'
flag=e 不支持
gbase> select foo from regexp_split_to_table('the quick brown fox jumps over the lazy dog','O','e') as foo ;
ERROR 1708 (HY000): [192.168.61.100:5050](GBA-02AD-0005)Failed to query in gnode:
DETAIL: Incorrect parameters in the call to native function 'regex_split_to_table'

flag=c 大小写敏感
gbase> select foo from regexp_split_to_table('the quick brown fox jumps over the lazy dog','O','c') as foo ;
+---------------------------------------------+
| foo                                         |
+---------------------------------------------+
| the quick brown fox jumps over the lazy dog |
+---------------------------------------------+
1 row in set (Elapsed: 00:00:00.01)
flag=i 大小写不敏感
gbase> select foo from regexp_split_to_table('the quick brown fox jumps over the lazy dog','O','i') as foo ;
+----------------+
| foo            |
+----------------+
| the quick br   |
| wn f           |
| x jumps        |
| ver the lazy d |
| g              |
+----------------+
5 rows in set (Elapsed: 00:00:00.00)
flag=m 换行敏感 (实际效果与n不同)
gbase> select foo from regexp_split_to_table('the quick brown fox jumps
   '> over the lazy dog','.o','m') as foo ;
+------------------------+
| foo                    |
+------------------------+
| the quick b            |
| wn                     |
| x jumps
over the lazy  |
| g                      |
+------------------------+
4 rows in set (Elapsed: 00:00:00.01)
gbase> select foo from regexp_split_to_table('the quick brown fox jumps
   '> over the lazy dog','o','m') as foo ;
+----------------+
| foo            |
+----------------+
| the quick br   |
| wn f           |
| x jumps
      |
| ver the lazy d |
| g              |
+----------------+
5 rows in set (Elapsed: 00:00:00.00)
flag= n 换行敏感 
gbase> select foo from regexp_split_to_table('the quick brown fox jumps
   '> over the lazy dog','.o','n') as foo ;
+---------------+
| foo           |
+---------------+
| the quick b   |
| wn            |
| x jumps       |
| ver the lazy  |
| g             |
+---------------+
5 rows in set (Elapsed: 00:00:00.01)
gbase> select foo from regexp_split_to_table('the quick brown fox jumps
   '> over the lazy dog','o','n') as foo ;
+----------------+
| foo            |
+----------------+
| the quick br   |
| wn f           |
| x jumps
      |
| ver the lazy d |
| g              |
+----------------+
5 rows in set (Elapsed: 00:00:00.01)
flag=p 不支持
gbase> select foo from regexp_split_to_table('the quick brown fox jumps
   '> over the lazy dog','.o','p') as foo ;
ERROR 1708 (HY000): [192.168.61.100:5050](GBA-02AD-0005)Failed to query in gnode:
DETAIL: Incorrect parameters in the call to native function 'regex_split_to_table'
flag=q 不支持
gbase> select foo from regexp_split_to_table('the quick brown fox jumps over the lazy dog','o','q') as foo ;
ERROR 1708 (HY000): [192.168.61.100:5050](GBA-02AD-0005)Failed to query in gnode:
DETAIL: Incorrect parameters in the call to native function 'regex_split_to_table'
flag=s 不支持
gbase> select foo from regexp_split_to_table('the quick brown fox jumps
   '> over the lazy dog','o','s') as foo ;
ERROR 1708 (HY000): [192.168.61.100:5050](GBA-02AD-0005)Failed to query in gnode:
DETAIL: Incorrect parameters in the call to native function 'regex_split_to_table'
flag=t 不支持
gbase> select foo from regexp_split_to_table('the quick brown fox jumps over the lazy dog','O','t') as foo;
ERROR 1708 (HY000): [192.168.61.100:5050](GBA-02AD-0005)Failed to query in gnode:
DETAIL: Incorrect parameters in the call to native function 'regex_split_to_table'
flag=w 不支持
gbase> select foo from regexp_split_to_table('the quick brown fox jumps over the lazy dog','O','w') as foo;
ERROR 1708 (HY000): [192.168.61.100:5050](GBA-02AD-0005)Failed to query in gnode:
DETAIL: Incorrect parameters in the call to native function 'regex_split_to_table'
flag=x 
gbase> select foo from regexp_split_to_table('the quick brown fox jumps over the lazy dog','O','x') as foo;
+---------------------------------------------+
| foo                                         |
+---------------------------------------------+
| the quick brown fox jumps over the lazy dog |
+---------------------------------------------+
1 row in set (Elapsed: 00:00:00.03)
gbase> select version();
+-------------------------------+
| version()                     |
+-------------------------------+
| 9.5.3.28.22_patch.4348cbd590f |
+-------------------------------+
1 row in set (Elapsed: 00:00:00.00)

4.等效函数
如果没有regexp_split_to_table函数，可以用unnest(string_to_array())替代