oracle 字符串变列,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'))

Oracle字符串转成Table列

标签：nec   info   null   substr   --   length   src   chart   table

本条技术文章来源于互联网，如果无意侵犯您的权益请点击此处反馈版权投诉 本文系统来源：https://www.cnblogs.com/LuckyZLi/p/12195514.html