Home » SQL & PL/SQL » SQL & PL/SQL » separating the number values with a decimal point
separating the number values with a decimal point [message #271649] Tue, 02 October 2007 06:00 Go to next message
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 #271652 is a reply to message #271649] Tue, 02 October 2007 06:26 Go to previous messageGo to next message
Michel Cadot
Messages: 68737
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
Quote:
What is need from me is starting from right I need to place “.” For separator.

Where?

Please read and follow OraFAQ Forum Guide.
Make sure that lines of code do not exceed 80 characters when you format. Use the "Preview Message" button.
Please always post your Oracle version (4 decimals).

Regards
Michel
Re: separating the number values with a decimal point [message #271653 is a reply to message #271649] Tue, 02 October 2007 06:29 Go to previous messageGo to next message
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.
Re: separating the number values with a decimal point [message #271660 is a reply to message #271649] Tue, 02 October 2007 07:06 Go to previous messageGo to next message
Frank
Messages: 7901
Registered: March 2000
Senior Member
oca_dwh wrote on Tue, 02 October 2007 13:00
Hello Everyone,
Please do help me in placing the decimal point between a number values.

Example:
The data I have is 654,715


Is 654,715 one number, or two numbers separated by a comma?
Re: separating the number values with a decimal point [message #271661 is a reply to message #271660] Tue, 02 October 2007 07:17 Go to previous messageGo to next message
Littlefoot
Messages: 21823
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Regarding this
Quote:
6.54,7.15
line, I'd say these are two numbers, separated by a comma.
Re: separating the number values with a decimal point [message #271663 is a reply to message #271661] Tue, 02 October 2007 07:33 Go to previous message
Michel Cadot
Messages: 68737
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
It would better if OP explain and precise this.
(And it would better to wat for his/her answer before posting solutions.) Razz

Regadrs
Michel
Previous Topic: Problem in deleting more than 50 laks of records. . .
Next Topic: How to start?
Goto Forum:
  


Current Time: Mon Feb 17 19:08:26 CST 2025