Ticket #30566
Question
I am trying to zero fill a number so it has leading zeroes and then concatenate it with another field. How can I accomplish this?
Answer
A numeric field can never be padded with leading zeros. If you have a numeric value, it must be first converted to a VARCHAR in a calculation, and then padded with zeros appropriately. All that can be done in a single alpha type database calculation like this (without the " " marks): "LPAD(CAST(COLUMN1 AS VARCHAR(6)),6,'0') || COLUMN2" This calculation is converting numeric COLUMN1 to a VARCHAR(6), and then being padded with 0's until the length is 6. The result is then concatenated with COLUMN2. Note the syntax in this suggested solution is valid for IBM iSeries databases. Please consult your database vendor's documentation if using a non-iSeries database.
Asked on September 9, 2020