RPGLE SQL Update a Substring


You are coding an rpgle program. Suppose you have a field that is 20 characters long and you want to update position 5 of it using embedded SQL. I know what you want to do. You want to

update yourFile set substr(field1, 5, 1) = '1' where blah

But you can't. It's not allowed.

However, there is a shamefaced workaround that will get the job done:

update yourFile set field1 = substr(field1, 1, 4) || '1' || substr(field1, 6, 15) where blah

Makes you feel dirty, doesn't it?

Apparently, the || shortcut messes up CCSIDs, but you can always go old school and do

update yourFile set field1 = concat(substr(field1, 1, 4), concat('1', substr(field1, 6, 15))) where blah

The same technique would be used if you were using green on black STRSQL and the Run SQL Scripts tool in i Navigator.