Padding columns using SQL

On cold morning in So Cal (we do get them) I was asked: "Is there any way to pad the values in FIELD1 in TESTFILE with zeroes?".

FIELD1 is a 20 long alphanumeric field in TESTFILE, despite its size only the first six characters are ever used.

....+....1....+....2 FIELD1 875 6891 3131 24187 40731 127963 684443

My mission was to add leading zeroes to the values that were not six long to make them that length.

This is something that I can easily do in RPG:

01 dcl-s Work char(6) ; 02 evalr Work = '000000' + %trimr(FIELD1) ; 03 FIELD1 = Work ; 04 update TESTFILER %fields(FIELD1) ;

But what is the fun in that? I have an excuse to find an alternative using SQL.

After a quick search in the IBM KnowledgeCenter I found a function that would fit my need: LPAD

It has three parameters:

1. Column name or string
2. Length of the result
3. Pad character(s), this can be one or multiple characters

I don't even need a program to do this. I can use my favorite SQL client (STRSQL or "Run SQL scripts" in either Operations Navigator or ACS).

UPDATE TESTFILE SET FIELD1 = LPAD(RTRIM(FIELD1),6,'0')

I need to use a right trim, RTRIM, to remove the trailing blank spaces after the value in the field.

6 means that I only want to pad up to six characters in length.

And the zero is what I want to pad with.

My results are exactly what I was asked for:

....+....1....+....2 FIELD1 000875 006891 003131 024187 040731 127963 684443

That is so simple, why would I want to write a RPG program to do the same thing.

If there is a LPAD, left pad, there has to be a RPAD, right pad, too.

RPAD uses the same three parameters as LPAD.

Again no need for a program I can just use my favorite SQL client again.

UPDATE TESTFILE SET FIELD1 = RPAD(RTRIM(FIELD1),15,'<---->')

I still need to trim the values in FIELD1.

This time I want to pad up to a length of 15.

And finally these are my pad characters: <---->

The results:

....+....1....+....2 FIELD1 875<----><----> 6891<----><---- 3131<----><---- 24187<----><--- 40731<----><--- 127963<----><-- 684443<----><--

The results show that the pad pattern is repeated up to the length of the value, 15, I gave.

This is another good example of the beauty and power of SQL. A program in RPG can be replaced by a single SQL statement.

 

You can learn more about this from the IBM website:

• LPAD
• RPAD

 

This article was written for IBM i 7.3, and should work for some earlier releases too.