Left-Justify or Right-Justify a Number and Remove Leading Zeros of a Number in a Character Field in SQL for IBM i

Normally with a number, it does not matter if a user puts in leading zeros during data entry. However, if the number is in a character field rather than a numeric field, there can be problems getting the numbers to look correct. Here are some statements that someone can use to try and get the numbers looking better while keeping them in a character field.

For the example, make a 6-digit field that is never filled with more than 3 (non-zero) digits. The most leading zeros used is 2. There is also a numeric field named NUM. Following is an example of how the data looks if it was in a numeric field:

 NUM   NUMINCHAR
  23   23
  23   0023
  43   0043
  43   43
  77   0077
 349   0349
 349   349
   1   1
   5   005

Using CASE statements, the following SQL statement will align the numbers:

  select num, numinchar,
  case substr(trim(l '0' from numinchar),2,1) when ' ' then
       '  '||trim(l '0' from numinchar)
  else case substr(trim(l '0' from numinchar),3,1) when ' ' then
       ' '||trim(l '0' from numinchar)
  else trim(l '0' from numinchar) end end as temp
  from lib/file
  order by temp

which gives the data:

  NUM   NUMINCHAR  TEMP
    1   1            1
    5   005          5
   23   23          23
   23   0023        23
   43   0043        43
   43   43          43
   77   0077        77
  349   0349       349
  349   349        349

This works; however, the many layers of CASE logic gets hard to read. This removes the leading zeroes and right-justifies the numbers as if they are in a numeric field. The TRIM function is used to take off the leading zeros. The SUBSTR is used on the result of the TRIM statement to look at 1 digit of the resulting number to plug into the CASE statement and decides how to display the final number for each iteration of the CASE statement. The logic is:

Is the second digit of the field (when it has no leading zeros) equal to a space?

Yes: Display that line as the number with two extra spaces at the front and trim leading zeros.
No: Is the third digit of the field (when it has no leading zeros) equal to a space?

Yes: Display that line as the number with an extra space at the front, and trim leading zeros.
No: Display the number with no leading zeros.

A simpler statement that does not right-justify the numbers is:

  select num, numinchar,
  trim(t ' ' from (trim(l '0' from numinchar))) as new
  from lib/file
  order by new

which gives the data:

  NUM   NUMINCHAR  NEW
    1   1          1
   23   23         23
   23   0023       23
  349   0349       349
  349   349        349
   43   0043       43
   43   43         43
    5   005        5
   77   0077       77

If the justification does matter, take the last statement one step further, going back into the CASE logic.

  select num, numinchar,
  trim(t ' ' from (trim(l '0' from numinchar))) as new,
  case length(trim(t ' ' from trim(l '0' from numinchar)))
      when 1 then '  '||(trim(t ' ' from trim(l '0' from numinchar)))
      when 2 then ' '||(trim(t ' ' from trim(l '0' from numinchar)))
      when 3 then trim(t ' ' from trim(l '0' from numinchar))
      end as bingo
  from lib/file
  order by bingo

which gives:

   NUM   NUMINCHAR  NEW       BINGO
     1   1          1           1
     5   005        5           5
    23   23         23         23
    23   0023       23         23
    43   0043       43         43
    43   43         43         43
    77   0077       77         77
   349   0349       349       349
   349   349        349       349

This is still easier to read than the first CASE statement because there is only one CASE with multiple options rather than nested CASE statements. The TRIM statement is always the same. In this statement, the TRIM statement removes the leading zeros. Then, another trim takes off the trailing spaces from what remains. This leaves only one number. Because only the number is left, the LENGTH function can be used in an SQL statement to decide how many spaces are required to pad the front of the number.

To go one step further than a SELECT statement, create a view that displays like the SQL statements above; however, they will not be in order because the ORDER BY clause is not allowed in a view. Here is an example of creating a view (without the ORDER BY clause) for the last two SQL statement examples:

  create view lib/view1 AS
  select num, numinchar,
  trim(t ' ' from (trim(l '0' from numinchar))) as new
  from lib/file

  create view lib/view2 AS
  select num, numinchar,
  trim(t ' ' from (trim(l '0' from numinchar))) as new,
  case length(trim(t ' ' from trim(l '0' from numinchar)))
      when 1 then '  '||(trim(t ' ' from trim(l '0' from numinchar)))
      when 2 then ' '||(trim(t ' ' from trim(l '0' from numinchar)))
      when 3 then trim(t ' ' from trim(l '0' from numinchar))
      end as bingo
  from lib/file

Another option is to change all of the data in the file to correct the leading zeros and right-justify the data. This could be done for the last example with the UPDATE statement of:

UPDATE lib/file SET NUMINCHAR =
   case length(trim(t ' ' from trim(l '0' from numinchar)))
       when 1 then '  '||(trim(t ' ' from trim(l '0' from numinchar)))
       when 2 then ' '||(trim(t ' ' from trim(l '0' from numinchar)))
       when 3 then trim(t ' ' from trim(l '0' from numinchar))
       end

Additional options: