Home » SQL & PL/SQL » SQL & PL/SQL » Formatting a number (Oracle 10.2.0.3.0)
Formatting a number [message #401597] Tue, 05 May 2009 13:09 Go to next message
oracle123
Messages: 56
Registered: March 2009
Member
Hi All,

I need help in formatting a column with datatype NUMBER(6,3).

Example data in that column is:
Quote:
123.456
52.49
97.000
123.496


I want to use some formatting/conversion technique on that column to output data like this:
Quote:
123.45600
52.49000
97.00000
123.49600


Basically, I want my output look like a number(6,5) right padded with zeroes. i tried with rpad but wasn't successful.

Thanks,
Scott.
Re: Formatting a number [message #401598 is a reply to message #401597] Tue, 05 May 2009 13:11 Go to previous messageGo to next message
joy_division
Messages: 4642
Registered: February 2005
Location: East Coast USA
Senior Member
Look up number formats in the manual.

Number(6,5) is does not match that data.
Re: Formatting a number [message #401599 is a reply to message #401597] Tue, 05 May 2009 13:14 Go to previous messageGo to next message
BlackSwan
Messages: 25045
Registered: January 2009
Location: SoCal
Senior Member
You need to help us by following the Posting Guidelines as stated below.
http://www.orafaq.com/forum/t/88153/0/
Go to the URL above click the link "Posting Guidelines"
Go to the section labeled "Practice" & do as directed.

Post DDL for tables.
Post DML for test data.

Post expected/desired results.
Re: Formatting a number [message #401600 is a reply to message #401597] Tue, 05 May 2009 13:18 Go to previous messageGo to next message
Michel Cadot
Messages: 64132
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
SQL Reference
Chapter 2 Basic Elements of Oracle SQL
Section Format Models

Regards
Michel
Re: Formatting a number [message #401601 is a reply to message #401597] Tue, 05 May 2009 13:21 Go to previous messageGo to next message
oracle123
Messages: 56
Registered: March 2009
Member
Joy Division,

Quote:
Basically, I want my output look like a number(6,5) right padded with zeroes. i tried with rpad but wasn't successful.


I am sorry. I want output look like a number(8,5). I am a rookie to Oracle development.

Thanks,
Scott.
Re: Formatting a number [message #401602 is a reply to message #401601] Tue, 05 May 2009 13:26 Go to previous messageGo to next message
joy_division
Messages: 4642
Registered: February 2005
Location: East Coast USA
Senior Member
Michel's link is what you would want. His message was more helpful than mine. I'm lazy, I am sorry. I didn't feel like going to the documentation and searching for it. I left that up to you Smile
Re: Formatting a number [message #401607 is a reply to message #401597] Tue, 05 May 2009 13:49 Go to previous messageGo to next message
oracle123
Messages: 56
Registered: March 2009
Member
Michel,

Thanks for the link. It helped a lot. I came up with the following query:

SELECT LENGTH(TO_CHAR('123.456', '999999.99999')),
       TO_CHAR('123.456', '999999.99999')
  FROM DUAL;


Result for the above query:
Quote:
13 123.45600


why does it convert it into a length of 13 when my format mask('999999.99999') is of length 12?

please explain.

scott.


Re: Formatting a number [message #401608 is a reply to message #401607] Tue, 05 May 2009 13:54 Go to previous messageGo to next message
Michel Cadot
Messages: 64132
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Read again carefully the meaning of "9" format element in the documentation and come back if you don't find the answer.

Clue: try a negative number.

Regards
Michel
Re: Formatting a number [message #401609 is a reply to message #401597] Tue, 05 May 2009 14:08 Go to previous messageGo to next message
oracle123
Messages: 56
Registered: March 2009
Member
Michel,

Quote:
Returns value with the specified number of digits with a leading space if positive or with a leading minus if negative.


It seems like adding a space as my query is for a positive number.

So is there a way I can avoid that leading space?

Thanks,
Scott.

Re: Formatting a number [message #401610 is a reply to message #401609] Tue, 05 May 2009 14:13 Go to previous messageGo to next message
BlackSwan
Messages: 25045
Registered: January 2009
Location: SoCal
Senior Member
oracle123 wrote on Tue, 05 May 2009 12:08

So is there a way I can avoid that leading space?



Why do you want to do that?
Re: Formatting a number [message #401612 is a reply to message #401597] Tue, 05 May 2009 14:22 Go to previous messageGo to next message
oracle123
Messages: 56
Registered: March 2009
Member
Blackswan,

Output of my code goes into a text file with the specified formats and lengths. Whereas for the column I am talking about is to be of the format '999999.99999' with a length of 12 characters.

Thanks,
scott.
Re: Formatting a number [message #401614 is a reply to message #401609] Tue, 05 May 2009 14:27 Go to previous messageGo to next message
Michel Cadot
Messages: 64132
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
So is there a way I can avoid that leading space?

The answer is in the page I posted.
First time in the examples, table 2-14.
Then in the "Format Model Modifiers" section below, see FM.

Regards
Michel

Re: Formatting a number [message #401615 is a reply to message #401612] Tue, 05 May 2009 14:37 Go to previous message
BlackSwan
Messages: 25045
Registered: January 2009
Location: SoCal
Senior Member
oracle123 wrote on Tue, 05 May 2009 12:22

the format '999999.99999' with a length of 12 characters.



What happens for -123456.54321 or similar large negative value?
Previous Topic: Reference to Program that Fired the Trigger
Next Topic: How to make the column heading to bold
Goto Forum:
  


Current Time: Wed Dec 07 16:16:15 CST 2016

Total time taken to generate the page: 0.11437 seconds