stringoracle-databasenumbers

ORACLE - String to number

I have a little problem with a column on a table. The column is a Varchar named "prize". The datas are something like:

00008599
00004565
00001600
etc...

They have to become:

85.99
45.65
16.00
etc...

I have tried with to_number function but it doesnt work. Something like:

SELECT to_number(prize, '999999.99') FROM TABLE

The error is: ORA-01722

Solution

  • You could use LTRIM to get rid of leading zeroes and divide by 100:

    SELECT to_number(ltrim(prize, '0')) / 100 FROM table