string informix string-concatenation sql-function leading-zero

Add leading zeros to number string on informix SQL

I am trying to find a cleaner way to add leading zeros to a number string on an Informix SQL query.

The field I'm selecting is an int that has values ranging to 4 to 14 digits. I need to convert this field to a string that always has 14 characters and fills the remaining positions with leading zeros, so 1234 becomes 00000000001234, 1234567 becomes 00000001234567 and 1234567890000 becomes 01234567890000. I am currently using the CASE function like so:

select case
    when length(to_char(num_field)) = 4 then CONCAT("0000000000",to_char(num_field))
    when length(to_char(num_field)) = 5 then CONCAT("000000000",to_char(num_field))
    when length(to_char(num_field)) = 6 then CONCAT("00000000",to_char(num_field))
    when length(to_char(num_field)) = 7 then CONCAT("0000000",to_char(num_field))
    when length(to_char(num_field)) = 8 then CONCAT("000000",to_char(num_field))
    when length(to_char(num_field)) = 9 then CONCAT("00000",to_char(num_field))
    when length(to_char(num_field)) = 10 then CONCAT("0000",to_char(num_field))
    when length(to_char(num_field)) = 11 then CONCAT("000",to_char(num_field))
    when length(to_char(num_field)) = 12 then CONCAT("00",to_char(num_field))
    when length(to_char(num_field)) = 13 then CONCAT("0",to_char(num_field))
else to_char(num_field) end num_field_with_zeros

Is there a function in Informix SQL that does this transformation in a more efficient way?

Solution

I think you're looking for TO_CHAR() with a numeric format string:

SELECT TO_CHAR(num_field, "&&&&&&&&&&&&&&") AS num_field_with_zeros
  FROM the_table;

When I checked this, I used:

SELECT TO_CHAR(tabid, '&&&&&&&&&')
  FROM "informix".systables
 WHERE tabid > 100;

and got the output (in one particular database):

I simply chose to limit the output to 9 characters instead of 14.