Oracle中如何使用REGEXP_SUBSTR，listagg函数

来源：互联网发布：大学如何提高自己知乎编辑：程序博客网时间：2024/06/06 21:42

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'的集合。

[plain] view plaincopy
--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  


with temp as(  
  select 'China' nation ,'Guangzhou' city from dual union all  
  select 'China' nation ,'Shanghai' city from dual union all  
  select 'China' nation ,'Beijing' city from dual union all  
  select 'USA' nation ,'New York' city from dual union all  
  select 'USA' nation ,'Bostom' city from dual union all  
  select 'Japan' nation ,'Tokyo' city from dual   
)  
select nation,listagg(city,',') within GROUP (order by city)  
from temp  
group by nation 

0 0