Menu
How can I convert my DECIMAL(11) field from 12345678 to a character value of 00012345678?
879
The LPAD function pads a string on the left with a specified character string or with blanks. LPAD ( string-expression , integer , pad ) The schema is SYSIBM. The LPAD function treats leading or trailing blanks in the string expression as significant.
Using ROUND() function: This function in SQL Server is used to round off a specified number to a specified decimal places. Using FLOOR() function: It returns the largest integer value that is less than or equal to a number.
5) you can use the SQL standard TRIM() function: db2 => CREATE TABLE tbl (vc VARCHAR(64)) DB20000I The SQL command completed successfully. db2 => INSERT INTO tbl (vc) VALUES ('00001 00'), ('00026 00') DB20000I The SQL command completed successfully.
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:
119
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
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
