dkg
dkg

Reputation: 57

Char in currency format to number

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

Answers (1)

Rahul Tripathi
Rahul Tripathi

Reputation: 172418

You can try like this:

select TO_Number('$119,252.75','$999,999,999.99') from dual

SQL FIDDLE DEMO

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

SQL FIDDLE DEMO

Upvotes: 3

Related Questions