SQL Help

[austingal]

I need to do a conversion on a field. The field is a numeric field and can be a negative value. I need to pad the field so that it is 5 in length (padding with zeros) (interface to a mainframe. Currently I use lpad(field, 5, 0) with no problems but now we have negative numbers and it is coming out 000-1

I have tried several thing using sign, substr etc with no luck. How do I search for the - sign, and insert zeros between it and the abs of the number?

I've been playing around with
rpad(substr('-', sign(-1), 1, 4, 0) || abs(-1) but it is formatting for both positive and negative and the value is not always -1...could be -12 etc.

Thanks,

Amy

 

[LKBrwnDBA]

If the number is negative and you "pad" to 5 characters, then you would onle have four significand digits (-0005)

Now, for different DBMS there may be different answer.

Oracle example:

TO_CHAR(field,'FM0000')




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

 

[austingal]

Thank you that was it! Would you please direct me to a place where I can get the list of those codes i.e. the FM0000...

Thank you!

Amy

 

[LKBrwnDBA]

Oracle SQL referance: Format models


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