Oracle字符串转成Table列
目录导航:
1. 使用正则表达式
2. 借助DB Function实现
1、使用正则表达式
WITH tb AS
(SELECT '0,1,2,3,4,5,6,7,8' i_name
FROM dual)
SELECT regexp_substr(i_name, '[^,]+', 1, LEVEL) COLUMN_VALUE
FROM tb
CONNECT BY PRIOR dbms_random.value IS NOT NULL
AND LEVEL <= length(i_name) - length(REPLACE(i_name, ',', '')) + 1;
2、借助DB Function实现
-- DB function
CREATE OR REPLACE FUNCTION STRINGTOVARCHARTABLE ( inputList in varchar2 ) return VarcharTableType
as
v_str long default inputList || ',';
v_n varchar2(4000);
v_data VarcharTableType := VarcharTableType();
begin
loop
v_n :=instr( v_str, ',' );
exit when (v_n is null);
v_data.extend;
v_data( v_data.count ) := ltrim(rtrim(substr(v_str,1,v_n-1)));
v_str := substr( v_str, v_n+1 );
end loop;
return v_data;
end;
SELECT DISTINCT (COLUMN_VALUE)
FROM TABLE(STRINGTONUMBERTABLE('0,1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23'))