DB2 SQL Convert Decimal to Character with Padded Zeros

sql db2
macunte picture macunte · Mar 20, 2013 · Viewed 96.9k times · Source

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

Answer

kelgwiin picture kelgwiin · Apr 21, 2015

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

Related questions

DB2 Query to retrieve all table names for a given schema

I'm just looking for a simple query to select all the table names for a given schema. For example, our DB has over 100 tables and I need to find any table that contains the sub-string “CUR”. I can use the …

Read More
show all tables in DB2 using the LIST command

This is embarrassing, but I can't seem to find a way to list the names of the tables in our DB2 database. Here is what I tried: root@VO11555:~# su - db2inst1 root@VO11555:~# . ~db2inst1/sqllib/db2profile …

Read More
DB2 Date format

I just want to format current date into yyyymmdd in DB2. I see the date formats available, but how can I use them? http://publib.boulder.ibm.com/infocenter/dzichelp/v2r2/index.jsp?topic=%2Fcom.ibm.db2z10.doc.…

Read More