Home » SQL & PL/SQL » SQL & PL/SQL » Numeric data type conversion (Merged)
Numeric data type conversion (Merged) [message #185499] Wed, 02 August 2006 01:17 Go to next message
Ashokragauv
Messages: 13
Registered: June 2006
Location: Pune
Junior Member

Hi gurus,

I have one table in that i have one amount column....

that column as follows

Tot_Amt
10455.68
5487.33
107.28
1587650.45
52458.07
545265877.63
as per my requirement i need this column values as follows

Tot_Amt_New
10.455.68(Ten Thousand four fifty five and sixty eight paise)
5.487.33
107.28
15.87.650.45
52.458.07
54.52.65.877.63

I want to this column to be delimited by dot(.) as per indian Rupees format

Ex 99999999.99 --> 9.99.99.999.99 (9 crores 99 laksh 99 thousand 999 Rupees 99 paise)

Thanks in advance

Numeric data type conversion-urgent [message #185510 is a reply to message #185499] Wed, 02 August 2006 01:45 Go to previous messageGo to next message
Ashokragauv
Messages: 13
Registered: June 2006
Location: Pune
Junior Member

Hi gurus,

I have one table in that i have one amount column....

that column as follows

Tot_Amt
10455.68
5487.33
107.28
1587650.45
52458.07
545265877.63
as per my requirement i need this column values as follows

Tot_Amt_New
10.455.68(Ten Thousand four fifty five and sixty eight paise)
5.487.33
107.28
15.87.650.45
52.458.07
54.52.65.877.63

I want to this column to be delimited by dot(.) as per indian Rupees format

Ex 99999999.99 --> 9.99.99.999.99 (9 crores 99 laksh 99 thousand 999 Rupees 99 paise)

Thanks in advance
Re: Numeric data type conversion-urgent [message #185513 is a reply to message #185510] Wed, 02 August 2006 01:50 Go to previous messageGo to next message
Littlefoot
Messages: 20901
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Use format mask and apply TO_CHAR function to the number, such as
SQL> select to_char(10455.68, '999,999,990.00') from dual;

TO_CHAR(10455.6
---------------
      10,455.68

Your example ("10.455.68") suggests that you don't distinguish decimal point and thousands separator; is it really so in India, or was it a mistake?

[EDIT]

I forgot to mention, but you can use D for a decimal separator and G for thousands separator instead of ". and/or ,"; this would make your code more portable as Oracle would replace D and G with according values set in your database. These can be set using NLS_NUMERIC_CHARACTERS in the initialization file, or using ALTER SESSION SET NLS_NUMERIC_CHARACTERS command during the current session. Its format is

NLS_NUMERIC_CHARACTERS = "<decimal_character><group_separator>"

[Updated on: Wed, 02 August 2006 02:02]

Report message to a moderator

Re: Numeric data type conversion-urgent [message #185516 is a reply to message #185499] Wed, 02 August 2006 01:57 Go to previous messageGo to next message
Littlefoot
Messages: 20901
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
What makes this question an expert one? Its another position seems to be a better choice.
Re: Numeric data type conversion-urgent [message #185537 is a reply to message #185513] Wed, 02 August 2006 03:46 Go to previous messageGo to next message
Ashokragauv
Messages: 13
Registered: June 2006
Location: Pune
Junior Member

Hi little foot Thanks for your reply. I am very new to Oracle. I am SQL server developer. Ok this is not indian money format. Just for understanding i mentioned like that. sorry for the mistake. and I need .(dot) instead of comma.

thanks for ur tips and keep it up....

With Lots of thanks

Ashok
Re: Numeric data type conversion-urgent [message #185573 is a reply to message #185537] Wed, 02 August 2006 06:53 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Merged two topics from different forums. Please do not cross-post.
Re: Numeric data type conversion-urgent [message #185655 is a reply to message #185573] Wed, 02 August 2006 21:52 Go to previous message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
Oracle NLS does not seem to support the GROUP separator (comma in most contries) being the same as the DECIMAL POINT character ('.' in most countries).

You can use the default settings of these two characters with the G and D format modifier in TO_CHAR.
select to_char(1000000, '99G99G999D99')
from dual;


Or you can override them as follows:
select to_char(1000000, '99G99G999D99', 'NLS_NUMERIC_CHARACTERS=''^#''')
from dual;


However you cannot set them to the same character.

Use REPLACE instead:
select replace(to_char(1000000, '99,99,999.99'),',','.')
from dual;



Ross Leishman
Previous Topic: count in SQL*LOADER
Next Topic: Regarding tables index by binary_integer
Goto Forum:
  


Current Time: Fri Dec 09 02:13:19 CST 2016

Total time taken to generate the page: 0.09083 seconds