Geek Answers Handbook

Oracle function parameters to_number

I am having problems with the second and third parameters of the TO_NUMBER function. Do any of them depend on the other? How does the nls_params parameter work? I can not understand how the result of the request

SELECT TO_NUMBER('17.000,23', '999G999D99', 'nls_numeric_characters='',.'' ') REFORMATTED_NUMBER FROM DUAL;

maybe 17000.23. Can someone explain the process of the above conversion.

PS The above query is taken from the Oracle Database SQL Expert certificate preparation book.

+4
source share
2 answers

Now I will answer my question. When using the TO_NUMBER function TO_NUMBER I missed the important point that everything I get from the TO_NUMBER function will be a number. And the number contains nothing but a decimal point and E scientific notation. So 17,788.99 is actually not a number, but rather a string representation of 17788.99.

If we try to subtract 500 from 17,788.99, we will fail. (Well, Oracle implicitly converts numeric strings to numbers and vice versa, but basically we cannot perform arithmetic between strings and numbers). I am sure that the TO_NUMBER function TO_NUMBER almost never used to select a column value. It was rather used to perform arithmetic operations. Instead, we use TO_CHAR to display the column value or any numeric expression in a neat, easy-to-read format. The fomat and nls_params models are designed not only for the TO_NUMBER function, but also for TO_CHAR .

+1
source

you tell the TO_NUMBER function, which, two characters TO_NUMBER in nls_numeric_characters represent decimal and thousandth order

 G (thousands seperator) = . D (decimal seperator) = ,

therefore he sees the number as seventeen thousand points twenty three.

see http://download.oracle.com/docs/cd/B13789_01/olap.101/b10339/x_stddev022.htm#i78653

+5
source

More articles:


All Articles