Use substring in RPGLE SQL Statements

Substring in RPGLE SQL

This morning, I had to make a little change to an RPG4 program to let it respond to a new parameter being passed into it via JSON.

This value had to be stored in the first character of a field in a physical file in the database.

Simple Logic and we want it done quick, efficient, simple and easy to maintain for future programmers.

This is something that we do all the time in high level business languages like RPG so it should be easy right?

Time for a quick little substring in RPGLE SQL

Put on my field=column|record=row|file=table hat

SQL is wonderful for database updates like this, but I quickly realized that IBM I SQL does not let you update a substring, a little snippet, within an existing column on the table.

Lets say we just wanted to update the first character of a field in the database (unimaginably) called myField:

update myFile set substr(myField, 1, 1) = 'A' where mykey = "something"

I was kind of amazed that it wouldn’t let me do it!

Luckily there is a nice little workaround.

When I say nice, I mean “kind of clunky”. But it works, so let’s not moan, shall we?

Given this example, I had to update just the first character of a column (MAPGE) replacing that character:1 with the value stored in a field called flagOptOutCellPhone :

// Update the first character of MAPGE on LMGEP with the new flag
// value of Y/N passed in via JSON
exec sql
  update LMGEP
  SET MAPGE = concat(flagOptOutCellPhone,substr(MAPGE ,2))
  where KYDTGE = myResKeyDate and SEQGE = myResSequence;

The code basically says update FIELD and set it to NEW VALUE and then glue on the rest of the old value starting from position 2.

I don’t like it.

But it works and delivers the result I wanted.

So, I guess I like it at the same time.