SQL> select data,translate(data,'0123456789','##########') as num1, replace(translate(data,'0123456789','##########'),'#','') as num2 from v; DATA NUM1 NUM2 -------------------- ---------- ------------------------------------------------------------------------------------------------------ SMITH 20 SMITH ## SMITH ALLEN 30 ALLEN ## ALLEN WARD 30 WARD ## WARD JONES 20 JONES ## JONES MARTIN 30 MARTIN ## MARTIN BLAKE 30 BLAKE ## BLAKE CLARK 10 CLARK ## CLARK SCOTT 20 SCOTT ## SCOTT KING 10 KING ## KING TURNER 30 TURNER ## TURNER ADAMS 20 ADAMS ## ADAMS JAMES 30 JAMES ## JAMES FORD 20 FORD ## FORD MILLER 10 MILLER ## MILLER 14 rows selected.