Why extra space in to_char conversion? 3

[nagornyi]

The query

select to_char(rownum, '00') from all_tables where rownum<21

adds extra space in front of each item. Why?

 

[SantaMufasa]

Nagornyi,

The "extra" space on the left of any number-to-character conversion in Oracle is the "sign byte" to accommodate the minus sign in case the numeric value goes negative.

If you wish to get rid of the "extra" space, you can modify your code to read:

select substr(to_char(rownum, '00'),-2) from all_tables where rownum<21;

SU
--
01
02
03
.
.
.
20

Let us know if this resolves your question.

Mufasa
(aka Dave of Sandy, Utah, USA)

http://www.dasages.com

Do you use Oracle and live or work in Utah, USA?
Then click here to join Utah Oracle Users Group on Tek-Tips.

 

[nagornyi]

I actually did that substr already and just was wondering why extra space. OK, now I understand. Thanks!

 

[BJCooperIT]

Or, if you are lazy like I am, use:

ltrim(to_char(rownum),' ')

^{Beware of false knowledge; it is more dangerous than ignorance. ~George Bernard Shaw}
Sr. App. Developer Analyst/Custom Forms & PL/SQL - Oracle/Windows
Author and Sole Proprietor of: Emu Products Plus

 

[LKBrwnDBA]

Use this better:

to_char(rownum, 'FM00')

----------------------------------------------------------------------------
The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

 

[nagornyi]

Two very good ideas. Thanks!

 

[engineer2100]

how come i donot get any extra space in the front when I ran it in toad?

-engi

 

[nagornyi]

Try

select length(to_char(rownum, '00')) from all_tables where rownum<2

What do you get? 2 or 3?