Reputation: 57
I need to convert a field that is in varchar but with a currency format into a number type.
The value comes in this format $999,999.99 but may also come as empty.
I have tried this but it doesn't seem to work
to_number(nvl(MYFIELD, '$0.00'),'FM$999,999,999,990.00')
Any ideas?
Upvotes: 1
Views: 2136
Reputation: 172418
You can try like this:
select TO_Number('$119,252.75','$999,999,999.99') from dual
Also your query is working fine for me
select to_number(nvl('$119,252.75', '$0.00'),'FM$999,999,999,990.00') from dual
Upvotes: 3