CODEWITHSUNDEEP
databaseoracle-databaseplsql
Ichiro Satoshi
Ichiro Satoshi

Reputation: 301

Change varchar data type to Number in Oracle

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

Upvotes: 2

Views: 4602

Answers (2)

Bob Jarvis - Слава Україні
Bob Jarvis - Слава Україні

Reputation: 50067

Another way to do this would be

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

Share and enjoy.

Upvotes: 0

Michael Pakhantsov
Michael Pakhantsov

Reputation: 25390

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

Upvotes: 6

Related Questions