Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question
add comment

2 Answers

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

share|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 '12 at 15:16
add comment

I was able to use a CASE statement to resolve this problem in my situation. APPLICATION_OID is a DECIMAL(17,0). You may have to tweek the ranges I the case statements to meet your needs.

SELECT LENGTH(CHAR(APPLICATION_OID)), APPLICATION_OID, CHAR(APPLICATION_OID), 
CASE WHEN APPLICATION_OID > 0 AND APPLICATION_OID <= 9 THEN SUBSTR(CHAR(APPLICATION_OID),18,1)
     WHEN APPLICATION_OID > 9 AND APPLICATION_OID <= 99 THEN SUBSTR(CHAR(APPLICATION_OID),17,2)
     WHEN APPLICATION_OID > 99 AND APPLICATION_OID <= 999 THEN SUBSTR(CHAR(APPLICATION_OID),16,3)
END
FROM ABCDEFG.APP_MSTR
WHERE APPLICATION_OID IN (57,1,2,3,4,5,6,7,8,9,10)
WITH UR;
share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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