CODEWITHSUNDEEP
sqloracle-databaseoracle11g
user466534
user466534

Reputation:

to_number function in sql

i could not understand why following code SQL> 

Select to_number('1234.64', '9999.9') from Dual;

returns this number 1234.6?is it something like rounding ,truncation or?please help me to understand this code,i know to_number functions,i have used many times this code for simple chars,but here it is not clear anything

Upvotes: 0

Views: 45648

Answers (2)

user359040
user359040

Reputation:

This looks a lot like Oracle, but I suspect that the result would be similar in any SQL that used to_number.

The to_number function takes two arguments: the string to be converted to a number, and the format string for the conversion.

In the example, '12345.64' is the string to be converted, while '9999.9' is the format string. In this format string, a 9 stands for a digit while a . stands for the decimal point.

So the function is asking to convert the string '12345.64' to a number with up to 4 digits to the right of the decimal point, and only 1 digit after the decimal point.

The second argument is optional - under normal circumstances, I would omit it.

Upvotes: 2

Marco
Marco

Reputation: 57583

You should use

SELECT to_number('1234.64', '9999.99') from Dual;

Your mask tells engine you want just one decimal, so number gets rounded.
If you want to get exact number, don't specify any mask:

SELECT to_number('1234.64') from Dual;

Upvotes: 0

Related Questions