up vote 4 down vote favorite
share [g+] share [fb]

I have a column named length defined as:

TYPE_NAME: DECIMAL 
DATA_TYPE: 3 
COLUMN_SIZE: 9 
BUFFER_LENGTH: 11
DECIMAL_DIGITS: 5 
NUM_PREC_RADIX: 10

When I run the following select:

select char(length) st_length, length from atable

I get leading zeros on the st_length column:

st_length   length
0043.00000  43.00000
0043.00000  43.00000
0045.00000  45.00000
0044.00000  44.00000
0044.00000  44.00000
0046.00000  46.00000

Why am I getting the leading zeros and how do I get rid of them?

Edit: I meant to say this earlier but forgot. I'm using db2 v9.5.8 LUW.

Resolution: My original goal was to get the left most character of the length. I ended up with this:

select left(char(cast(length as int)),1) st_length, length from atable

which yielded,

st_length  length
4          43.00000
4          43.00000
4          45.00000
4          44.00000
4          44.00000
4          46.00000

Improvements are welcome.

link|improve this question
feedback

1 Answer

up vote 2 down vote accepted

It appears by design. See behaviour changes for v9.7 for more.

I suggest you'd need VARCHAR inside LPAD with leading spaces

link|improve this answer
Thank you for pointing that out. I missed that in the docs. Also, it didn't help that I was looking at v9.7 docs when referencing the CHAR function and it specifically said leading zeros are omitted. Most db2 instances here are v9.7 but when I double-checked, this one is v9.5. So, VARCHAR won't work either, according to the v9.5 docs decimal is not an acceptable input to the VARCHAR function, and indeed doesn't work when tried. – DMKing Feb 22 at 15:16
feedback

Your Answer

 
or
required, but never shown
discard

Not the answer you're looking for? Browse other questions tagged or ask your own question.