Use substring in RPGLE SQL Statements

So, 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. 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 doesnt 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 wouldnt let me do it!

Luckily there is a nice little workaround. When I say nice, I mean “kind of clunky”. But it works, so lets 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 dont like it.

But I like it at the same time.