patternMinor
Why do I get leading zeros when converting from decimal to char in a db2 query?
Viewed 0 times
leadingwhyzeroscharquerydecimaldb2getwhenconverting
Problem
I have a column named length defined as:
When I run the following select:
I get leading zeros on the st_length column:
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:
which yielded,
Improvements are welcome.
TYPE_NAME: DECIMAL
DATA_TYPE: 3
COLUMN_SIZE: 9
BUFFER_LENGTH: 11
DECIMAL_DIGITS: 5
NUM_PREC_RADIX: 10When I run the following select:
select char(length) st_length, length from atableI 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.00000Why 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 atablewhich yielded,
st_length length
4 43.00000
4 43.00000
4 45.00000
4 44.00000
4 44.00000
4 46.00000Improvements are welcome.
Solution
It appears by design. See behaviour changes for v9.7 for more.
I suggest you'd need VARCHAR inside LPAD with leading spaces
I suggest you'd need VARCHAR inside LPAD with leading spaces
Context
StackExchange Database Administrators Q#13611, answer score: 3
Revisions (0)
No revisions yet.