转载 Oracle字符的拼接和拆解

一、字符串拼接方法 LISTAGG

1、基本语法：listagg('p1', 'p2') within group(order by 'p3') over (partition by 'p4')

p1: 需要进行拼接的字段，一般是分组条件查询后存在多个值的字段
p2: 指定什么符号作为连接分隔符，例如：','
p3: 这个参数是被按照用来对 p1 指定的字段进行排序的
p4: 这个参数类似于 group by 条件语句。group by 语句需要将查询的参数都置于其后这样会显得累赘，而 partition 则不会存在这个问题，相对要灵活一点。

2、完整用例

// 简单的用法，将查询出的所有ID字段进行拼接，这也是我最常用的
SELECT LISTAGG(A.ACPE_ID, ',') WITHIN GROUP(ORDER BY A.ACPE_ID)
  FROM BD_CRM_ACPE A;

// 加上 GROUP BY 语句后的使用方式
SELECT A.ACPE_TYPE,
       LISTAGG(A.ACPE_ID, ',') WITHIN GROUP(ORDER BY A.ACPE_ID)
  FROM BD_CRM_ACPE A
 GROUP BY A.ACPE_TYPE;

// 这种查询会得到与上一种相同的结果
SELECT A.ACPE_TYPE,
       LISTAGG(A.ACPE_ID, ',') WITHIN GROUP(ORDER BY A.ACPE_ID) OVER(PARTITION BY A.ACPE_TYPE)
  FROM BD_CRM_ACPE A;

二、字符串的拆解方法

要完成字符串按照特定符号拆解成多行的效果，需要了解 oracle 内置的三种方法的用法。当然如果觉得麻烦也可以自己创建一个函数来处理。

1.正则匹配截取字符串函数 REGEXP_SUBSTR

语法：regexp_substr( str, regexp, start-pos, choice)
str：要被处理的字符串
regexp：正则表达式匹配模式
start-pos：从第几个字符开始匹配，默认为1
choice：返回第几个匹配到的数据

SELECT REGEXP_SUBSTR('1,2,3', '[^,]+', 1, 1) FROM DUAL;
返回：1
正则：'[^,]+' 表示匹配一个或多个不包含 ',' 的字符

2.条件语句 CONNECT BY

该语句一般是用于递归查询数据库中存在上下级关系的数据，但这里只是用它来获取一个连续的值，用于REGEXP_SUBSTR函数的choice参数

// 这样会返回三个值：1、2、3
SELECT ROWNUM FROM DUAL CONNECT BY ROWNUM <= 3;

3.正则匹配字符替换函数 REGEXP_REPLACE

因为这里的需求是分割字符串，现在需要知道分割后到底有几个值，这个值需要赋值给 CONNECT BY 语句后。
所以现在的解决思路是先得到原来字符串的长度，然后将符号替换掉，再求出长度，然后之前的长度减去现在去掉符号后的长度然后加一就是我们需要的值了。

length('1,2,3') = 5
length('123') = 3
length('1,2,3') - length('123') + 1 = 3

语法：REGEXP_REPLACE(source_char, pattern , replace_string)
source_char：需要替换字符的原始字符串
pattern：正则匹配需要被替换的字符
replace_string：替换后的字符

SELECT REGEXP_REPLACE('1,2,3', ',' ,'') FROM DUAL;
最终得到：'123'

三、方法整合

 // 最终的完整语句，你只需要将 '1,2,3' 替换成需要拆分的表字段
 SELECT REGEXP_SUBSTR('1,2,3', '[^,]+', 1, ROWNUM) FROM DUAL
CONNECT BY ROWNUM <= LENGTH('1,2,3') - LENGTH(REGEXP_REPLACE('1,2,3', ',' ,'')) + 1

转载 https://www.jianshu.com/p/3ccc45ea3953