Change varchar data type to Number in Oracle

Question

I have data like this in oracle database -> 20123,45 ,data type is varchar. and I want to migrate it to column with NUMBER data type, change comma (,) to dot (.). so expected result is 20123.45 and data type is NUMBER,

how can I do that ?

thanks before :D

Answer 1

use

to_number(YourColumnName, FormatMask, NLS_NUMERIC_CHARACTERS = ',.') from ...

select to_number('12345.667677', '99999999D999999', 'NLS_NUMERIC_CHARACTERS = ''.,''') 
from dual

select to_number('12345,667677', '99999999D999999', 'NLS_NUMERIC_CHARACTERS = '',.''') 
from dual

Answer 2

Another way to do this would be

SELECT TO_NUMBER(REPLACE('12345,6789123', ',', '.')) FROM DUAL;

Share and enjoy.