Oracle listagg() 中的子字符串去重 regexp_replace_oracle listagg函数 正则去重

1 概述

2 语法

2.1 regexp_replace()：字符串替换

-- 将 '源字符串' 中 符合 '正则表达式' 的字符串替换为 '新字符串'
regexp_replace('源字符串', '正则表达式', '新字符串')

-- 去除 子串 中的数字（数字 替换为 空）
select regexp_replace('abc123', '[0-9]', '') -- abc
  from dual;
1
2
3
4
5
6

2.2 listagg()：先排序

regexp_replace(listagg('列1', ',') within group(order by '列2'),
               '([^,]+)(,\1)*(,|$)',
               '\1\3')

-- 相邻相同字符串去重，故一定要 先排序！
select regexp_replace('1,1,3,5,5', '([^,]+)(,\1)*(,|$)', '\1\3') 
  from dual; 
-- 结果: 1,3,5
1
2
3
4
5
6
7
8

参数解释：源字符串 = '1,1,3,5,5’

-- ([^,]+)(,\1)*(,|$) 由 3 个括号组成
([^,]+): 1个或多个非 ',' 的数据，如: 1 1 3 5 5
(,\1)* : 括号1 ', 隔开 2个重复数据'，如: (1,1),(3),(5,5)   
(,|$)  : 末尾添加 ',' 或 '$'，如: (1,1,)(3,)(5,5,)

with t_str as (
  select '1,1,3,5,5' str from dual
)
select t.str, -- 1,1,3,5,5
       regexp_replace(t.str, '([^,]+)', 'x') a, -- x,x,x,x,x
       regexp_replace(t.str, '([^,]+)(,\1)*', 'x') b, -- x,x,x
       regexp_replace(t.str, '([^,]+)(,\1)*(,|$)', 'x') c -- xxx
  from t_str t;


\n: 匹配第 n 个 () 中的引用（'重复一次'）
'(\d)\1': 匹配两个连续的数字，如：11aa 中的 11
'(\d)(a)\1': 匹配第一个是数字，第二个是a，第三个引用第一个数字，如：1a1
'(\d)(a)\2': 如：1aa
'(\d)(a)\1\2': 如：1a1a

select regexp_replace('11aa', '(\d)\1', 'x') a, -- xaa
       regexp_replace('1a1a', '(\d)(a)\1', 'x') b, -- xa
       regexp_replace('1aa1', '(\d)(a)\2', 'x') c, -- x1
       regexp_replace('1a1aa', '(\d)(a)\1\2', 'x') d -- xa
  from dual;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26

3 扩展

3.1 Oracle 正则表达式详解：regexp_xx

• Oracle 正则表达式详解（regexp_substr、regexp_instr、regexp_replace、regexp_like）