按特定字符拆分字符串

开发过程中经常会遇到将前台多个值用逗号连接一同传递到后台查询，这个用逗号连接的字符串分隔的每个字符串分别对应Oracle数据库表的不同行。

如下一个表table_test的内容如下：

name value

pa 5

pb 6

pc 8

需要查询分别与pa和pb相同的行，参数字符串为：

pi_names=”pa,pb”

如何查询呢，有以下三种方式（根据执行计划分析，效率由低到高排列）：

1. 使用Oracle Database 11g Release 2及更高版本时，可以使用递归with子查询：

with a(name, i)

as

(select regexp_substr(pi_names, '[^,]+') name,

substr(pi_names || ',', instr(pi_names, ',') + 1) i

from dual

union all

select regexp_substr(i, '[^,]+'), substr(i, instr(i, ',') + 1)

from a

where instr(i, ',') <> 0)

select t.name, t.value from table_test t inner join a on a.name = t.name

2. 使用connect by语句实现之方式一：

with a as

(select distinct regexp_substr(pi_names, '[^,]+', 1, level) name

from dual

connect by regexp_substr(pi_names, '[^,]+', 1, level) is not null

)

select t.name, t.value from table_test t inner join a on a.name = t.name

3. 使用connect by语句实现之方式二：

with a as

(select regexp_substr(pi_names, '[^,]+', 1, rownum) name

from dual

connect by rownum <= length(pi_names) -

length(replace(pi_names, ',')) + 1)

select t.name, t.value from table_test t inner join a on a.name = t.name

4

with temp0 as (select LEVEL lv from dualCONNECT BY LEVEL <= 100)

select id,

substr(t.vals,instr(t.vals, ' ', 1, tv.lv)+ 1,instr(t.vals, ' ', 1, tv.lv + 1)-(instr(t.vals, ' ', 1, tv.lv) + 1)) ASattr4

from

(select id,' ' || attr4 || ' ' ASvals,length(attr4 || ' ') - nvl(length(REPLACE(attr4, ' ')), 0) AS cnt

fromT_FLIGHT_TLX_TOTAL where rownum < 3) t

join temp0 tv on tv.lv <= t.cnt order by 1;

按序号
合并
第一种 select deptno,listagg(ename,',') within group (order by empno) from emp group by deptno;
第二种 select deptno,wm_concat(ename) from emp group by deptno;