OraFAQ Forum: SQL & PL/SQL » adding trailing zeros

Home » SQL & PL/SQL » SQL & PL/SQL » adding trailing zeros

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

adding trailing zeros [message #117666]

Thu, 28 April 2005 15:42

abeiko
Messages: 29
Registered: March 2005

Junior Member

I have a field that is number(10,3). I need a built-in function that will add a zero onto the end of the number if there is only 2 digits after the decimal. For example, if the number is 85.23 I need it to be 85.230. But if the number is 85.123, I want it left alone.
I have tried using Column <column_name> format 999.990, but that isn't working.

Any help would be appreciated.

Report message to a moderator

Re: adding trailing zeros [message #117669 is a reply to message #117666]

Thu, 28 April 2005 16:04

smartin
Messages: 1803
Registered: March 2005
Location: Jacksonville, Florida

Senior Member

Look into the rpad() function, which you can call after converting your number to a character with to_char().

Report message to a moderator

Re: adding trailing zeros [message #117679 is a reply to message #117669]

Thu, 28 April 2005 16:46

abeiko
Messages: 29
Registered: March 2005

Junior Member

Rpad will add enough zeros to make the field length equal to 10. There is no way to tell it to add zeros only if the digits after the decimal don't equal 3.
I only want a zero at the end if it doesn't have 3 digits after the decimal place. I need all numbers in the column to be formatted to three decimal places (number 10,3).

Again -- 85.12 should come out as 85.120
85.123 should come out as 85.123

Report message to a moderator

Re: adding trailing zeros [message #117698 is a reply to message #117679]

Fri, 29 April 2005 00:21

Barbara Boehmer
Messages: 9106
Registered: November 2002
Location: California, USA

Senior Member

You can use to_char or column ... format or set numformat, as demonstrated below.

scott@ORA92> clear columns
scott@ORA92> select to_char (your_number, '99990.000') as column_name
  2  from (select 85.23 as your_number from dual
  3  	   union all
  4  	   select 85.123 as your_number from dual)
  5  /

COLUMN_NAM
----------
    85.230
    85.123

scott@ORA92> clear columns
scott@ORA92> column column_name format 9990.000
scott@ORA92> select to_char (your_number, '99990.000') as column_name
  2  from (select 85.23 as your_number from dual
  3  	   union all
  4  	   select 85.123 as your_number from dual)
  5  /

COLUMN_NAM
----------
    85.230
    85.123

scott@ORA92> clear columns
scott@ORA92> set numformat 9990.000
scott@ORA92> select to_char (your_number, '99990.000') as column_name
  2  from (select 85.23 as your_number from dual
  3  	   union all
  4  	   select 85.123 as your_number from dual)
  5  /

COLUMN_NAM
----------
    85.230
    85.123

scott@ORA92>

Report message to a moderator

Re: adding trailing zeros [message #118144 is a reply to message #117666]

Tue, 03 May 2005 05:04

rumasinha
Messages: 12
Registered: May 2005
Location: Bangalore

Junior Member

create table temp
( a number(10,3));

insert into temp values (85.23);
insert into temp values (85.123);

column a format 9999999.999;
select * from temp;

A
--------
85.230
85.123

column a format 9999999.990;
select * from temp;

A
--------
85.230
85.123

Report message to a moderator

Previous Topic:	query help
Next Topic:	utl_file exceptions not being raised

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Thu Aug 21 11:20:24 CDT 2025