DB2 SQL Convert Decimal to Character with Padded Zeros

Question

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

score 18 · Answer 1 · edited May 07 '20 at 13:43

18

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

edited May 07 '20 at 13:43

not2savvy

2,902
3
22
37

answered Apr 21 '15 at 15:12

kelgwiin

766
1
12
23

You may use `RIGHT()` around it to gain the desired number of digits. – not2savvy May 07 '20 at 13:00

score 11 · Answer 2 · answered Mar 21 '13 at 13:51

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

score 10 · Answer 3 · answered Mar 20 '13 at 23:35

10

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

answered Mar 20 '13 at 23:35

AjV Jsy

5,799
4
34
30

I'm getting LPAD in *LIBL type *N not found. – macunte Mar 21 '13 at 00:25
Well, I tried, not a db2 user :) .... see here too http://stackoverflow.com/questions/4964244/pad-varchar-numbers-with-0s-in-db2 – AjV Jsy Mar 21 '13 at 09:41
1

LPAD function is available only on the DB2 for `zOS`. – serhio Jan 17 '14 at 17:19
lpad( cast( F2 as int),5,'0') this worked for me on iseries – danny117 Jul 10 '15 at 15:04

score 3 · Answer 4 · edited Apr 21 '15 at 15:17

3

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.

edited Apr 21 '15 at 15:17

ProgramFOX

6,131
11
45
51

answered Oct 03 '13 at 18:38

Filipe

31
1

score 1 · Answer 5 · answered Apr 22 '15 at 06:46

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.

Adam111p · Accepted Answer · 2018-12-28T08:29:25.583

1

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

edited Dec 28 '18 at 08:29

answered May 24 '17 at 12:50

Adam111p

3,469
1
23
18

score 1 · Answer 7 · edited Nov 09 '18 at 13:54

1

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

edited Nov 09 '18 at 13:54

Laurenz Albe

209,280
17
206
263

answered Nov 09 '18 at 13:48

user10629183

11
1

score 0 · Answer 8 · answered Mar 21 '13 at 02:03

0

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

answered Mar 21 '13 at 02:03

macunte

463
2
8
18

2

What does that have to do with the question? I don't see any need of CASTing to INT. – user2338816 Apr 23 '15 at 08:57
1

The db2 lpad function appears to be overloaded with integer as the first argument. – danny117 Apr 28 '17 at 15:34

score 0 · Answer 9 · answered Aug 27 '18 at 08:37

0

Try this for your field x:

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

answered Aug 27 '18 at 08:37

funnyfish

133
9

score -1 · Answer 10 · edited Mar 16 '18 at 06:12

-1

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

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

edited Mar 16 '18 at 06:12

TarangP

2,711
5
20
41

answered Mar 15 '18 at 18:19

Not an expert

1

The question has nothing to do with dates. – SovietFrontier Dec 07 '18 at 15:54

DB2 SQL Convert Decimal to Character with Padded Zeros

10 Answers10