使用XML转换功能进行字符串的分割:
DECLARE
v_csvlist VARCHAR2(100) := '10.01.03.04.234';
v_xmllist XMLTYPE :=
XMLTYPE('<coll><e>' || REPLACE(v_csvlist,'.','</e><e>') || '</e></coll>');
BEGIN
FOR r IN (
SELECT EXTRACTVALUE(xt.column_value,'e') AS element
FROM TABLE(XMLSEQUENCE(EXTRACT(v_xmllist,'/coll/e'))) xt
)
LOOP
DBMS_OUTPUT.PUT_LINE(r.element);
END LOOP;
END;
结果显示:
10
01
03
04
234
简单表示:
SELECT EXTRACTVALUE(xt.column_value,'e')
FROM TABLE(XMLSEQUENCE
( EXTRACT
( XMLTYPE('<coll><e>' ||
REPLACE('10.01.03.04.234','.','</e><e>') ||
'</e></coll>')
, '/coll/e') )) xt;
结果显示: