Split String in PL/SQL using a simple select statement

Note that the input string is: '10.01.03.04.234'

And delimited specified is: ‘.’

 

SELECT to_number(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

 

This PLSQL procedure splits a string into numbers PLSQL Table.

 

DECLARE

    v_csvlist VARCHAR2(100) := '10,01,03,04,234';

    v_xmllist XMLTYPE :=

        XMLTYPE('<coll><e>' || REPLACE(v_csvlist,',','</e><e>') || '</e></coll>');

    TYPE number_list IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;

    n_list number_list;

    l_count BINARY_INTEGER := 1;

BEGIN

    FOR r IN (

       SELECT EXTRACTVALUE(xt.column_value,'e') AS element

        FROM   TABLE(XMLSEQUENCE(EXTRACT(v_xmllist,'/coll/e'))) xt

    )

   LOOP

            n_list(l_count) := to_number(r.element);

            dbms_output.put_line(n_list(l_count));

            l_count:=l_count+1;

   END LOOP;

 

END;