DB2 SQL Convert Decimal to Character with Padded Zeros

How can I convert my DECIMAL(11) field from 12345678 to a character value of 00012345678?

10 Answers

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 RIGHTfunctions:

SELECT RIGHT(REPEAT('0', 11) || LTRIM(CHAR(your_field)), 11) FROM your_table 

Using for details on CAST
and for string functions,
I assume this should do the trick -

SELECT LPAD( CAST(FIELD AS CHAR(11)) ,11,'0') AS PADDEDFIELD

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.

My LeftPad function without LeftPad function

 REPEAT('0', 4-length(MY_COLUMN_VALUE))||CHAR(MY_COLUMN_VALUE) as NEW_COLUMN MY_COLUMN_VALUE NEW_COLUMN 1 0004 23 0023 

testing ...

SELECT '32' MY_VALUE, REPEAT('0', 4-length('23'))||CHAR('23') as LEFTPAB_MY_VALUE FROM sysibm.sysdummy1 

If this is DB2 for i, and myColumn data type is DECIMAL with precision (11) and scale (0), then:

SELECT digits( myColumn ) FROM sysibm.sysdummy1 

will return:

....+....1. DIGITS 00001234567 

Changing the number of leading zeros could be done in many ways. CASTing to a different precision before using DIGITS() is one way.

SELECT SUBSTRING( CHAR(100000000000+fieldName), 2,11 ) as paddedFieldName FROM yourTable 

I only wanted to define my field once in the select statement so the above worked for me and is tidy

I just went the other direction: cast(field as int)

Try this for your field x:

substr(digits(x), 33 - length(x), length(x) ) 

From Numeric 8,0 (datenumfld=20170101) to 01/01/2017 This works for me:

DATE(TO_DATE(CHAR(datenumfld), 'YYYYMMDD')) as YourDate