How to separate the number from varchar2 column?

Question

I have column with value (25KG) and the column type is varchar2 I need to separate the number from the value !

Answer 1

A simple option - based on what you posted - is to use regular expressions:

SQL> with test (col) as
  2    (select '25KG' from dual)
  3  select regexp_substr(col, '\d+') result
  4  from test;

RE
--
25

SQL>