ORACLE - String to number

Question

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

Gratt

Do you have values in the column that aren't pure numbers? You can check with something like 'not regexp_like(prize, '^[0-9]+$')` – Cracksman

If you can (after finding the data that doesn't conform), you should update your data model so that prize is stored as number and even preferably number(n,2) for some suitable value of n. – Rives

Answer 1

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

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

Rumery

Thank you. I was using this instruction that involve string functions but your is better to_number( SUBSTR(prize,0,5)||'.'||SUBSTR(prize,6,2)) as realPrize – Gratt

Answer 2

Please notice that you yourself have to handle the fact that the string is 100 times to great. Easiest way should be something like this:

SELECT to_number(prize)/100 FROM TABLE

Chromatolysis

Answer 3

While the other two answers give solutions, they don't explain what's causing the problem to begin with.

Part of the problem is that neither the Oracle documentation nor the Oracle error message are clear. Only a string (or the display of a number) has a format. In the Oracle TO_NUMBER function, the 2nd "format" parameter tells Oracle how to interpret the first parameter, not how to display the result. The Oracle documentation does not make this clear.

Unfortunately, the Oracle documentation uses the same page to describe format codes for both the TO_NUMBER and TO_CHAR function. Even worse, the format code page is written from the perspective of the TO_CHAR function; that is, it says a "0" will display leading zeros. And that's just wrong for the TO_NUMBER function.

And to compound things further, the ORA-01722 "invalid number" error message isn't very clear. It's saying that the string provided as the first parameter isn't valid with respect to the format code in the 2nd parameter. Why isn't it valid? Because the format code includes a decimal point and the first parameter doesn't include a decimal point. So Oracle can't parse the first parameter according to the "rule" given in the 2nd parameter. You can confirm this by running the TO_NUMBER function without the optional 2nd parameter...

SELECT TO_NUMBER('00001600') FROM DUAL;

...which dutifully reports 1600, showing that there is nothing inherently "invalid" about the numeric VARCHAR with leading zeros. Oracle is presumably using some kind of default format string, but I'm not seeing anything explicit about this in the Oracle TO_NUMBER or Format Model documentation.

You can the reverse problem if the format code has no decimal point but the numeric string does. This also gives an "invalid number" error...

SELECT TO_NUMBER('123.45', '999999')

... I can't even truncate using a format code of '999'. This also gives ORA-01722 "Invalid Number":

SELECT TO_NUMBER('123.45', '999') FROM DUAL;

But adding a decimal point to the format code gives the expected result (of 123.45):

SELECT TO_NUMBER('123.45', '999.99')

I'm resorting to these belabored examples because I can't point to Oracle documentation that explains what the format code actually does in the TO_NUMBER function: It tells Oracle how to parse the first parameter, not how to display it.

One last example to drive that home: If the format code includes a comma and a trailing 0 after the decimal point, they do not appear in the results. This...

SELECT TO_NUMBER('123,456.78', '999,999.990') FROM DUAL;

...gives a result of 123456.78. (Tested in SQL*Plus and SQL Developer.)

While the other answers on the page work, it would also work to use the TO_NUMBER function correctly to begin with.

First, convert the VARCHAR in the first parameter to a pure number using the TO_NUMBER function, remembering that a pure number has no inherent format. Format is only set on display:

SELECT TO_NUMBER('00001600', '99999999') FROM DUAL;

Notice the 2nd parameter is describing how Oracle should interpret the first number; that is, interpret every character as a digit.

Second, divide the number by 100 to get the desired magnitude. (I'm showing "progressive enhancement" of the same query that is above):

SELECT TO_NUMBER('00001600', '99999999')/100 FROM DUAL; 

Finally, now that you have a pure number of the desired magnitude, you can tell Oracle how to display it using the TO_CHAR function:

SELECT TO_CHAR(TO_NUMBER('00001600', '99999999')/100, '9999.99') FROM DUAL;

This solution is more verbose than the other posts, so I don't mean to say it's better than the other solutions. I only hope it shows why the query in the original question didn't give the expected results.

Accentual