HiveBrain v1.2.0
Get Started
← Back to all entries
patternMinor

Why do I get leading zeros when converting from decimal to char in a db2 query?

Submitted by: @import:stackexchange-dba··
0
Viewed 0 times
leadingwhyzeroscharquerydecimaldb2getwhenconverting

Problem

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.

Solution

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

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.