TO_CHAR creates extra leading blank

[jfcox]

Hi,

I have to convert a number column that has leading zero(s) to a varchar2. If I run the TO_Char(column, '099999') or other similar formats I get an extra leading blank. For example, if the value in the number column is 012345 when I run the to_char I get ' 012345'. I have tried TRIM and LTRIM without success. Neither of these remove the leading blank. My guess is that it is not an ascii blank but some other non-pritable character.

Anybody have any ideas?

Jim

 

[SantaMufasa]

Jim,

I'm certain we can resolve this for you. Could you please post the following:

1) a copy-and-paste of the code and its results that you have tried.
2) a copy-and-paste of a "SELECT dump(to_char(column, '099999'));"

Mufasa
(aka Dave of Sandy, Utah, USA @ 19:01 (25Feb04) UTC (aka "GMT" and "Zulu&quot, 12:01 (25Feb04) Mountain Time)

 

[jfcox]

Great! Here's the sql and the result:

Select TO_CHAR(AHFS, '099999') NEW_AHFS from FDB_GCNSEQNO_AHFS

240408
240408
240408
240408
240408
240408
240408

And here is the result of the dump:

Typ=1 Len=7: 32,50,52,48,52,48,56
Typ=1 Len=7: 32,50,52,48,52,48,56
Typ=1 Len=7: 32,50,52,48,52,48,56
Typ=1 Len=7: 32,50,52,48,52,48,56
Typ=1 Len=7: 32,50,52,48,52,48,56
Typ=1 Len=7: 32,50,52,48,52,48,56
Typ=1 Len=7: 32,50,52,48,52,48,56
Typ=1 Len=7: 32,50,52,48,52,48,56
Typ=1 Len=7: 32,50,52,48,52,48,56
Typ=1 Len=7: 32,50,52,48,52,48,56
Typ=1 Len=7: 32,50,52,48,52,48,56
Typ=1 Len=7: 32,50,52,48,52,48,56

Thanks!

 

[SantaMufasa]

JF,

I don't know how you were doing your ltrim (when it was unsuccessful), but here are my results:

SQL> select '['||to_char(240408,'099999')||']' a,
  2  '['||ltrim(to_char(240408,'099999'))||']' c
  3* from dual
SQL> /

Original   Trimmed
TO_CHAR    TO_CHAR
---------- ----------
[ 240408]  [240408]

Let me know your findings.

Mufasa
(aka Dave of Sandy, Utah, USA @ 19:49 (25Feb04) UTC (aka "GMT" and "Zulu&quot, 12:49 (25Feb04) Mountain Time)

 

[jfcox]

Mufasa,

Thanks for the help. Based on what you sent, I fixed it. What I was doing was assuming that the extra blank was coming from the source table. I had this

Select To_Char(ltrim(column), '099999') instead of the
correct Select ltrim(To_Char(column, '099999'))

I hate it when I do stupid things. Thanks again for your help.

Jim

 

[SantaMufasa]

JF,

To solve the "Mystery of the Leading Blank", Oracle appends what appears to be a leading blank onto the display of number fields to accommodate a (negative) sign, should it be necessary. You must LTRIM it off if you don't want it.

Mufasa
(aka Dave of Sandy, Utah, USA @ 20:20 (25Feb04) UTC (aka "GMT" and "Zulu&quot, 13:20 (25Feb04) Mountain Time)

 

[ddrillich]

Please try:
select cast(001 as varchar2(10)) from dual;

Regards,
Dan

 

[dsanchez2]

Try:
[tt]
to_char(column,'FM099999')
[/tt]
The FM in the format removes leading and trailing blanks.