Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: Fixed length zero padded number format?
Paul Brewer wrote:
>
> "Richard Kuhler" <noone_at_nowhere.com> wrote in message
> news:JNoda.10899$UE.5971796_at_twister.socal.rr.com...
> > Is there a format model that will give a fixed length zero padded number
> > for both positive and negative values?
> >
> > It's the way Oracle handles signs that causes the problems. For
> > positive values it puts in a space where I want a zero (or the digit
> > from the number). Currently I have to use 2 different format models and
> > a case statement. For example ...
> >
> > TO_CHAR(NUM_COLUMN,
> > CASE WHEN NUM_COLUMN < 0
> > THEN 'S00'
> > ELSE 'FM000'
> > END)
> It's leaving room for a minus sign, in case it's needed.
> Just use LTRIM(TO_CHAR(NUM_COLUMN,'picture')).
I don't think you read the question. It's not just a matter of getting rid of the space. In fact, you give a poor solution for that as you can just use the 'FM' format prefix for that (like I am). The problem is that a negative number has a different length than a positive number with Oracle's formatting. That obviously doesn't work for a fixed length field in an extract file.
Anybody have the answer?
Thanks,
Richard Kuhler
Received on Mon Mar 17 2003 - 20:25:37 CST