or maybe TRIM??
Jay, October 15, 2007 - 9:53 am UTC
select length(trim(to_char (1, '000'))) from dual
Thanks!
October 15, 2007 - 12:22 pm UTC
why use yet another function when the formats have the innate capability to do what is required?
Johny Alex, October 15, 2007 - 10:14 am UTC
Tom,
Does it mean that the first place is ALWAYS reserverd for the "sign"?
October 15, 2007 - 12:25 pm UTC
if you don't use fm, they set aside an extra space for the sign when you use a fixed width format by default, you can move it around where ever you like - using the format.
Robert Massey, October 15, 2007 - 10:30 am UTC
Johny,
You can force the minus sign to the end by using:
select to_char(1, '000mi') from dual
@Johny Alex: first position always blank (for sign)?
Duke Ganote, October 15, 2007 - 11:46 am UTC
Implied by the documentation: "Without FM, the result of a character element is always right padded with blanks to a fixed length, and leading zeroes are always returned for a number element".
http://tinyurl.com/2yfycz Since numbers could be positive or negative, and the format length is fixed...
I'm reminded about the old joke about the mathematics instructor who was lecturing and writing a dense, multi-line proof on the board when he stated ".. and it's obvious that...". A student cried out from the class: "it's obvious?!?!".
The instructor stopped, and stared at the board for 5 minutes, pondering the implications of the equations.
Then turned to the class and said "yes, yes, it IS obvious."
@Duke and Robert
Johny Alex, October 16, 2007 - 8:09 am UTC
Thanks.
It is clear now.
Formatting using fm
Maverick, October 19, 2007 - 1:35 pm UTC
Tom,
I am using this query to get a number with 2 decimal places always, after rounding the decimals
select to_char(round(1890.4567,2),'fm999999999999999.99') from dual;
I get
1890.46
select to_char(round(1890.00,2),'fm999999999999999.99') from dual;
I get
1890.
Why??
it should either put 2 zeroes after decimal or remove decimal . But I'm getting "1890."
How to get rid of that decimal if I have 00 as decimal value[or retain 00]?
Patrick wolf, October 19, 2007 - 4:49 pm UTC
Use
select to_char(round(1890.00,2),'fm999999999999990.00') from dual;
to contain the decimal digits. With the 0 in front you will also always have a 0 before the point in case the value is below 1.
Patrick
formats that confused us all at one time
Stew, October 19, 2007 - 4:57 pm UTC
First, to Duke Ganote, good one! :-)
Maverick wrote:
> it should either put 2 zeroes after decimal or remove decimal . But I'm getting "1890."
With a format string like 'fm99999.99', you *should* get "1890." for an input value of "1890.00".
I think instead you want:
'fm9999.00' which forces the trailing zeros.
Check the format template documentation again.
I think that Maverick was looking for this:
Franco, October 26, 2007 - 10:28 am UTC
select (case
when substr(to_char(round(:my_value,2),'fm9G999G999G999D99'), -1) in (',','.') then
substr(to_char(round(:my_value,2),'fm9G999G999G999D99'), 1, length(to_char(round(:my_value,2),'fm9G999G999G999D99'))-1)
else
to_char(round(:my_value,2),'fm9G999G999G999D99')
end) this
from dual
Somebody can correct me, but I think that he should also loose the "round", it will round anyway:
select (case
when substr(to_char(:my_value,'fm9G999G999G999D99'), -1) in (',','.') then
substr(to_char(:my_value,'fm9G999G999G999D99'), 1, length(to_char(:my_value,'fm9G999G999G999D99'))-1)
else
to_char(:my_value,'fm9G999G999G999D99')
end) this
from dual
To Maverick again...
Franco, October 26, 2007 - 10:48 am UTC
Sorry, looking better at the docs:
select to_char(round(:my_value,2),'TM')from dual