with test as
      (select replace ((select trim(regexp_replace('123456789','(.)','\1 ')) from dual),' ',',') col from dual)
    select regexp_substr(col, '[^,]+', 1, level) result
    from test
    connect by level <= length(regexp_replace(col, '[^,]+')) + 1; 

(select replace ((select trim(regexp_replace('123456789','(.)','\1 ')) from dual),' ',',') col from dual)

(select regexp_replace('123456789','(.)','\1,') from dual)

SQL> with test as
  2    (select '123456' col from dual)
  3  select substr(col, level, 1) res
  4  from test
  5  connect by level <= length(col);

RES
----
1
2
3
4
5
6

6 rows selected.

SQL>

SELECT  SUBSTR('123456',LEVEL,1) VAL
  FROM  DUAL
  CONNECT BY LEVEL <= LENGTH('123456')
/

V
-
1
2
3
4
5
6

6 rows selected.

SQL>