Skip to Main Content
  • Questions
  • Trailing blank space (empty string) when converting number to character

Breadcrumb

Question and Answer

Tom Kyte

Thanks for the question, Johny.

Asked: October 15, 2007 - 9:46 am UTC

Last updated: October 15, 2007 - 12:25 pm UTC

Version: 10.2.0

Viewed 10K+ times! This question is

You Asked

SELECT TO_CHAR (1, '000') FROM DUAL

Why does this query brings a empty string at the beginning?
The result is " 001" instead of "001"

SELECT LENGTH (TO_CHAR (1, '000')) FROM DUAL

That returns 4. This should have been 3

Thanks in advance
Johny

and Tom said...

because of the space for the leading minus sign.

if you want, you can use the fm (format modifier) to have all extraneous whitespace removed:

ops$tkyte%ORA10GR2> select '"' || to_char( 1, '000' ) || '"',
  2  '"' || to_char( -1, '000' ) || '"',
  3  '"' || to_char( 1, 'fm000' ) || '"',
  4  '"' || to_char( -1, 'fm000' ) || '"'
  5  from dual;

'"'||T '"'||T '"'||T '"'||T
------ ------ ------ ------
" 001" "-001" "001"  "-001"



works with dates as well as numbers and such:

  1  select '"' ||to_char( sysdate, 'Month' )||'"',
  2  '"'||to_char( sysdate, 'fmMonth' )||'"'
  3* from dual
ops$tkyte%ORA10GR2> /

'"'||TO_CHA '"'||TO_CHA
----------- -----------
"October  " "October"

Rating

  (10 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

or maybe TRIM??

Jay, October 15, 2007 - 9:53 am UTC

select length(trim(to_char (1, '000'))) from dual

Thanks!
Tom Kyte
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"?
Tom Kyte
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