SQL> get is_number;
  1  Create or Replace
  2  Function IS_number
  3   (vString       in Varchar2,
  4    nStart_pos    in Number,
  5    nStop_Pos     in Number)
  6  Return varchar2 is
  7  nTest_Num     Number := 0;
  8  nbegin_pos    Number := Nvl(nStart_pos, 1);
  9  nEnd_Pos      Number :=  nvl(nStop_Pos, Length(vString));
 10  vSuccess_Flag Varchar2(1);
 11  Begin
 12     Begin
 13        IF vString is Null Then
 14           nTest_Num := 'test';
 15        End IF;
 16        nTest_Num     := To_Number(substr(vString, nBegin_Pos, nEnd_Pos));
 17        vSuccess_Flag := 'Y';
 18      Exception
 19          When Invalid_Number Then
 20               vSuccess_Flag := 'N';
 21          When Others Then
 22               vSuccess_Flag := 'N';
 23     END;
 24     Return vSuccess_Flag;
 25* END;
SQL> /

Function created.

SQL> get rip_number;
  1  create or replace function rip (f_in varchar2)
  2  return    varchar2 as
  3  v_in      varchar2(30);
  4  opt       varchar2(30);
  5  retval    varchar2(30);
  6  dumm      varchar2(30);
  7  begin
  8  v_in:=replace(replace(f_in,'0',' '),' ');
  9  for mag in 1..length(v_in) loop
 10     opt:=substr(v_in,mag,1);
 11     if is_number(opt,1,1)='Y'  then
 12     dumm:=dumm||opt;
 13     end if;
 14     end loop;
 15  return dumm;       
 16* end;
SQL> /

Function created.

SQL> select * from test10;

COL1
------------------------------
0078965432
24579893
END 23794832
IN 0078434521 MIDDLE

<b>By makin use of the functions created above </b>

SQL> select rip(col1) from test10;

RIP(COL1)
----------------------------------------------------------------------------------------------------
78965432
24579893
23794832
78434521

SQL>