DB2 SQL Convert Decimal to Character with Padded Zeros
Don't know if you've worked it out, however try this:
SELECT LPAD( DIGITS( fieldName ), 11, '0') ) as paddedFieldName FROM yourTable
The LPAD is the left padding, but the DIGITS function was the only way I got DB2 to treat the numeric value like a string.
Only use the DIGITS
function, because this verifies the length of the field numeric or decimal, etc and completes with zeros to the left when is necessary.
SELECT DIGITS(FIELD) FROM ...
The length of the resulting string is always:
- 5 if the argument is a small integer
- 10 if the argument is a large integer
- 19 if the argument is a big integer
Based on your comment in @Mr Fuzzy Botton's answer, I'm guessing you're on DB2 for i
, which does not have the LPAD
function. You could instead use a combination of the REPEAT
and RIGHT
functions:
SELECT RIGHT(REPEAT('0', 11) || LTRIM(CHAR(your_field)), 11)
FROM your_table
Using http://pic.dhe.ibm.com/infocenter/dzichelp/v2r2/topic/com.ibm.db2.doc.sqlref/castsp.htm for details on CAST
and http://pic.dhe.ibm.com/infocenter/dzichelp/v2r2/topic/com.ibm.db2z10.doc.sqlref/src/tpc/db2z_scalarfunctionsintro.htm for string functions,
I assume this should do the trick -
SELECT LPAD( CAST(FIELD AS CHAR(11)) ,11,'0') AS PADDEDFIELD