separating the number values with a decimal point [message #271649] |
Tue, 02 October 2007 06:00  |
oca_dwh
Messages: 3 Registered: October 2007
|
Junior Member |
|
|
Hello Everyone,
Please do help me in placing the decimal point between a number values.
Example:
The data I have is 654,715
What is need from me is starting from right I need to place “.” For separator.
6.54,7.15
Please do advice me what function should be used.
Thank you
|
|
|
|
Re: separating the number values with a decimal point [message #271653 is a reply to message #271649] |
Tue, 02 October 2007 06:29   |
 |
Littlefoot
Messages: 21823 Registered: June 2005 Location: Croatia, Europe
|
Senior Member Account Moderator |
|
|
Do you have to DISPLAY those values as you've said, or do you have to STORE those values into a table?
If you are displaying them, divide the number by 100 and apply the TO_CHAR function with the required format. For example:
SQL> select to_char(654/100, '999G999D99') res_1,
2 to_char(654/100, '999,999.99') res_2
3 from dual;
RES_1 RES_2
----------- -----------
6.54 6.54 Note two different ways of doing the same thing: using the hard-coded decimal point and thousands separator (, and .) or using (G and D) instead, which is preferable way to do that. Set those values in the NLS_NUMERIC_CHARACTERS parameter.
If you are, however, storing those values into a table, just divide them by 100 and INSERT (or UPDATE) into a table:SQL> insert into test (col) values (654/100);
1 row created.
SQL> select col from test;
COL
----------
6.54 Take care about proper formatting afterwards, during the SELECT operation.
|
|
|
|
|
|