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.