Oracle数据库的两个字段值为逗号分割的字符串，例如：字段A值为“1,2,3,5”，字段B为“2”。
想获取两个字段的交集（相同值）2，获取两个字段的差集（差异值）1,3,5。
一、最终实现的sql语句
1、获取交集（相同值）：
select regexp_substr(id, '[^,]+', 1, rownum) id from (select '1,2,3,5' id from dual) connect by rownum <= length(regexp_replace(id, '[^,]+')) +1 intersect -- 取交集 select regexp_substr(id, '[^,]+', 1, rownum) id from (select '2' id from dual) connect by rownum <= length(regexp_replace(id, '[^,]+')) +1; /*结果： 2 */
2、获取差集（差异值）：
select regexp_substr(id, '[^,]+', 1, rownum) id from (select '1,2,3,5' id from dual) connect by rownum <= length(regexp_replace(id, '[^,]+')) +1 minus --取差集 select regexp_substr(id, '[^,]+', 1, rownum) id from (select '2' id from dual) connect by rownum <= length(regexp_replace(id, '[^,]+')) +1; /*结果： 1 3 5 */
二、实现过程用到的函数用法说明
1、regexp_substr
正则表达式分割字符串，函数格式如下：
function regexp_substr(strstr, pattern [,position] [,occurrence] [,modifier] [subexpression]) __srcstr：需要进行正则处理的字符串 __pattern：进行匹配的正则表达式 __position：可选参数，表示起始位置，从第几个字符开始正则表达式匹配（默认为1） __occurrence：可选参数，标识第几个匹配组，默认为1 __modifier：可选参数，表示模式（'i'不区分大小写进行检索；'c'区分大小写进行检索。默认为'c'。）
使用例子：
select regexp_substr('1,2,3,5','[^,]+') AS t1, regexp_substr('1,2,3,5','[^,]+',1,2) AS t2, regexp_substr('1,2,3,5','[^,]+',1,3) AS t3, regexp_substr('1,2,3,5','[^,]+',1,4) AS t4, regexp_substr('1,2,3,5','[^,]+',2) AS t5, regexp_substr('1,2,3,5','[^,]+',2,1) AS t6, regexp_substr('1,2,3,5','[^,]+',2,2) AS t7 from dual; /*结果： 1 2 3 5 2 2 3 */
2、regexp_replace
通过正则表达式来进行匹配替换，函数格式如下：
function regexp_substr(srcstr, pattern [,replacestr] [,position] [,occurrence] [,modifier]) __srcstr：需要进行正则处理的字符串 __pattern：进行匹配的正则表达式 __replacestr：可选参数，替换的字符串，默认为空字符串 __position：可选参数，表示起始位置，从第几个字符开始正则表达式匹配（默认为1） __occurrence：可选参数，标识第几个匹配组，默认为1 __modifier：可选参数，表示模式（'i'不区分大小写进行检索；'c'区分大小写进行检索。默认为'c'。）
使用例子1：
select regexp_replace('1,2,3,5','5','4') t1, regexp_replace('1,2,3,5','2|3',4) t2, regexp_replace('1,2,3,5','[^,]+') t3, regexp_replace('1,2,3,5','[^,]+','') t4, regexp_replace('1,2,3,5','[^,]+','*') t5 from dual; /*结果： 1,2,3,4 1,4,4,5 ,,, ,,, *,*,*,* */
使用例子2（截取字符串中的指定字符）：
select regexp_replace('同意（72小时自动确认）--张三(2015-01-02 08:50:13);不同意。说明--李四(2022-01-20 12:20:17);同意。测试。--王五(2022-01-20 13:20:28);','(\d)|(不?同意\S*--)|(小时自动确认)|[。（）(): -]','') res from dual; /* 结果： 张三;李四;王五; */
使用例子3（截取字符串中的指定字符）：
/*说明： json内容：[{"advantage":"未知","disadvantage":"未知","unitName":"XX公司","unitRemark":""},{"advantage":"未知","disadvantage":"未知","unitName":"YY公司","unitRemark":""}] 正则说明：* 和 + 限定符都是贪婪的，它们会尽可能多的匹配文字，在它们后面加上一个 ? 就可以实现非贪婪或最小匹配 */ select regexp_replace(json, '(\{"advantage\S*?unitName":")|(","unitRemark\S*?\})|\[|\]', '') from tb /* 结果：XX公司,YY公司 */
使用例子4（替换字符串中的日期）：
select '测试 (2022-09-01)', regexp_replace('测试 (2022-09-01)', '(\d{4}-\d{2}-\d{2})', to_char(sysdate,'yyyy-mm-dd')) from dual; /*结果： 测试 (2022-09-01) 测试 (2022-09-29) */
3、connect by
（1）connect by单独用，返回多行结果
select rownum from dual connect by rownum < 5; /*结果： 1 2 3 4 */
select 'A' || to_char(41 + level - 1) from dual connect by level <= 80 - 41 + 1; /*结果： A41 A42 ...... A80 */
（2）一般通过start with . . . connect by . . .子句来实现SQL的层次查询
select id, name, sys_connect_by_path(id,'\') idpath, sys_connect_by_path(name, '\') namepath from ( select 1 id, '广东' name, 0 pid from dual union select 2 id, '广州' name , 1 pid from dual union select 3 id, '深圳' name , 1 pid from dual ) start with pid = 0 connect by prior id = pid; /*结果： 1 广东 \1 \广东 2 广州 \1\2 \广东\广州 3 深圳 \1\3 \广东\深圳 */
三、总结
由上面函数用法，可知下面语句可以把字符串“1,2,3,5”转换为4行记录
select regexp_substr(id, '[^,]+', 1, rownum) id from (select '1,2,3,5' id from dual) connect by rownum <= length(regexp_replace(id, '[^,]+')) +1
然后在2个结果中使用集合运算符（UNION/UNION ALL 并集，INTERSECT 交集，MINUS 差集）进行最终处理。
