Oracle 两个逗号分割的字符串,获取交集、差集(sql实现过程解析)_oracle_

 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 */

 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 */

 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; 

 function regexp_substr(srcstr, pattern [,replacestr] [,position] [,occurrence] [,modifier]) __srcstr：需要进行正则处理的字符串 __pattern：进行匹配的正则表达式 __replacestr：可选参数，替换的字符串，默认为空字符串 __position：可选参数，表示起始位置，从第几个字符开始正则表达式匹配（默认为1） __occurrence：可选参数，标识第几个匹配组，默认为1 __modifier：可选参数，表示模式（'i'不区分大小写进行检索；'c'区分大小写进行检索。默认为'c'。）

 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; 

 select rownum from dual connect by rownum < 5;

 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;

 select regexp_substr(id, '[^,]+', 1, rownum) id from (select '1,2,3,5' id from dual) connect by rownum <= length(regexp_replace(id, '[^,]+')) +1