WITH t1 AS
  ( SELECT 1 id ,'1234 567 89 0' str FROM dual
  UNION ALL
  SELECT 2 id ,'as&df dfg fg g' str FROM dual
  UNION ALL
  SELECT 3 id , 'gg' str FROM dual
  UNION ALL
  SELECT 4 id , NULL str FROM dual
  )
select t1.*,dbms_xmlgen.convert(xmlcast(column_value as varchar2(10)),1) column_value
 from t1, xmltable(nvl(rtrim(regexp_replace(dbms_xmlgen.convert(str)||' ', '(\S+)\s+', '"\1",'), ', '), '1 to 0')) x
/

        ID STR            COLUMN_VALUE
---------- -------------- ------------
         1 1234 567 89 0  1234
         1 1234 567 89 0  567
         1 1234 567 89 0  89
         1 1234 567 89 0  0
         2 as&df dfg fg g as&df
         2 as&df dfg fg g dfg
         2 as&df dfg fg g fg
         2 as&df dfg fg g g
         3 gg             gg

9 rows selected.

WITH t1 AS
  ( SELECT 1 id ,'1234 567 89 0' str FROM dual
  UNION ALL
  SELECT 2 id ,'as&df dfg fg g' str FROM dual
  UNION ALL
  SELECT 3 id , 'gg' str FROM dual
  UNION ALL
  SELECT 4 id , NULL str FROM dual
  )
select t1.*,dbms_xmlgen.convert(xmlcast(column_value as varchar2(10)),1) column_value
 from t1, 
xmltable(nvl(rtrim(regexp_replace(dbms_xmlgen.convert(str)||' ','(\S+)\s+','"\1",'),', '),'""'))(+) x -- ну и "" вместо 1 to 0 - буковок меньше
/

        ID STR            COLUMN_VALUE
---------- -------------- --------------
         1 1234 567 89 0  1234
         1 1234 567 89 0  567
         1 1234 567 89 0  89
         1 1234 567 89 0  0
         2 as&df dfg fg g as&df
         2 as&df dfg fg g dfg
         2 as&df dfg fg g fg
         2 as&df dfg fg g g
         3 gg             gg
         4

10 rows selected.