Home » SQL & PL/SQL » SQL & PL/SQL » pad number when no decimals
pad number when no decimals [message #423111] Tue, 22 September 2009 03:37 Go to next message
keizersoz
Messages: 16
Registered: April 2006
Junior Member
Hello,

Can someone please tell me how I should pad a number without decimals. For example I would like to display 700,00 instead of 700 which comes from DB.

What I did is:
TRIM(TO_CHAR(ROUND(I.INVOICE_TAX_AMOUNT,CUR.DECIMALS),'99999999999990D' || LPAD('9', cur.DECIMALS, '9')))

I'm padding the number with zeros, however I can do this only with strings. Moreover, when I convert the above result to numbers, the trailing padding is removed.

thanks for your quick reply
Re: pad number when no decimals [message #423113 is a reply to message #423111] Tue, 22 September 2009 03:48 Go to previous messageGo to next message
ayush_anand
Messages: 417
Registered: November 2008
Senior Member
lpad ('string', n [, 'string_pad')
rpad ('string', n [, 'string_pad')
string is left padded to length n with string_pad. If string_pad is ommited, a space will be used as default rpad is similar, but pads right instead of left.
SQL>  select lpad('100',10,0) from dual;

LPAD('100'
----------
0000000100
Re: pad number when no decimals [message #423115 is a reply to message #423111] Tue, 22 September 2009 03:55 Go to previous message
Michel Cadot
Messages: 64102
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
I would like to display 700,00 instead of 700

SQL> select to_char(700,'fm990D00') from dual;
TO_CHAR
-------
700.00

1 row selected.


Quote:
when I convert the above result to numbers, the trailing padding is removed.

This is meaningless, a number does NOT have heading or trailing 0. This is a display notion not a mathematical one.

Regards
Michel

[Updated on: Tue, 22 September 2009 03:57]

Report message to a moderator

Previous Topic: output lines for a select query
Next Topic: Update an unprocessed record exclusively
Goto Forum:
  


Current Time: Fri Dec 02 14:30:46 CST 2016

Total time taken to generate the page: 0.06011 seconds