3,499
社区成员
发帖
与我相关
我的任务
分享
--定义一个类型
CREATE OR REPLACE TYPE type_split IS TABLE OF VARCHAR2 (4000);
--创建函数
CREATE OR REPLACE FUNCTION SPLIT(P_SOURCE VARCHAR2, P_SEP VARCHAR2)
RETURN TYPE_SPLIT
PIPELINED IS
L_IDX PLS_INTEGER;
V_LIST VARCHAR2(50) := P_SOURCE;
BEGIN
LOOP
L_IDX := INSTR(V_LIST, P_SEP);
IF L_IDX > 0 THEN
PIPE ROW(SUBSTR(V_LIST, 1, L_IDX - 1));
V_LIST := SUBSTR(V_LIST, L_IDX + LENGTH(P_SEP));
ELSE
PIPE ROW(V_LIST);
EXIT;
END IF;
END LOOP;
RETURN;
END SPLIT;
--调用
select * from table(SPLIT('22,3,1,4',','))
order by to_number(column_value)
column_value
----------------------
1 1
2 3
3 4
4 22
select * from(
select to_number(regexp_substr('2,3,1,5,6,8,9,32,34,234,1,43,5,76,8','[^,]+',1,rownum)) rn
from dual connect by rownum<= length(regexp_replace('2,3,1,5,6,8,9,32,34,234,1,43,5,76,8','[^,]+'))+1)
order by rn asc