Wednesday, January 23, 2019

Padding columns using SQL

padding fields 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:

 

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

1 comment:

  1. Hi Simon, another easy way is like this,
    UPDATE TESTFILE SET FIELD1=
    DIGITS(CAST(FIELD1 AS DEC(6,0)))

    ReplyDelete

To prevent "comment spam" all comments are moderated.
Learn about this website's comments policy here.

Some people have reported that they cannot post a comment using certain computers and browsers. If this is you feel free to use the Contact Form to send me the comment and I will post it for you, please include the title of the post so I know which one to post the comment to.