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.
Scroll Up