1.使用层次结构connect by ...:
select * from(select regexp_substr(replace('11110xx1xx2222','0xx1xx',','), '[^,]+', 1, rownum) n
from dual
connect by rownum < 50) where n is not null
2使用自定义的分拆函数split,:
首先需要定义 2 个类型
1. Row 类型
CREATE OR REPLACE TYPE ty_row_str_split as object (strValue VARCHAR2 ( 4000 ))
2. Table 类型
CREATE OR REPLACE TYPE ty_tbl_str_split IS TABLE OF ty_row_str_split
创建函数:
CREATE OR REPLACE FUNCTION fn_split(p_str IN VARCHAR2 ,
p_delimiter IN VARCHAR2 )
RETURN ty_tbl_str_split IS
j INT : = 0 ;
i INT : = 1 ;
len INT : = 0 ;
len1 INT : = 0 ;
str VARCHAR2 ( 4000 );
str_split ty_tbl_str_split : = ty_tbl_str_split();
BEGIN
len : = LENGTH(p_str);
len1 : = LENGTH(p_delimiter);
WHILE j < len LOOP
j : = INSTR(p_str, p_delimiter, i);
IF j = 0 THEN
j : = len ;
str : = SUBSTR(p_str, i);
str_split.EXTEND;
str_split(str_split. COUNT ) : = ty_row_str_split(strValue => str );
IF i >= len THEN
EXIT ;
END IF ;
ELSE
str : = SUBSTR(p_str, i, j - i);
i : = j + len1;
str_split.EXTEND;
str_split(str_split. COUNT ) : = ty_row_str_split(strValue => str );
END IF ;
END LOOP;
RETURN str_split;
END fn_split;
使用样例:
select to_number(strvalue) as Value from table (fn_split( ' 1,2,3 ' , ' , ' ))
select to_char(strvalue) as Value from table (fn_split( ' aa,bb,cc ' , ' , ' ))