Use substring in RPGLE SQL Statements 

 August 1, 2017

By  NickLitten

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 :

RPG Code Snippet substring in RPGLE SQL// 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.


IBM i Software Developer, Digital Dad, AS400 Anarchist, RPG Modernizer, Shameless Trekkie, Belligerent Nerd, Englishman Abroad and Passionate Eater of Cheese and Biscuits. Nick Litten Dot Com is a mixture of blog posts that can be sometimes serious, frequently playful and probably down-right pointless all in the space of a day. Enjoy your stay, feel free to comment and remember: If at first you don't succeed then skydiving probably isn't a hobby you should look into.

Nick Litten

related posts:

{"email":"Email address invalid","url":"Website address invalid","required":"Required field missing"}
__CONFIG_colors_palette__{"active_palette":0,"config":{"colors":{"cff50":{"name":"Main Accent","parent":-1},"a344d":{"name":"Accent Transparent","parent":"cff50"}},"gradients":[]},"palettes":[{"name":"Default","value":{"colors":{"cff50":{"val":"var(--tcb-skin-color-0)"},"a344d":{"val":"rgba(46, 138, 229, 0.85)","hsl_parent_dependency":{"h":210,"l":0.54,"s":0.78}}},"gradients":[]},"original":{"colors":{"cff50":{"val":"rgb(0, 178, 255)","hsl":{"h":198,"s":1,"l":0.5}},"a344d":{"val":"rgba(0, 178, 255, 0.85)","hsl_parent_dependency":{"h":198,"s":1,"l":0.5}}},"gradients":[]}}]}__CONFIG_colors_palette__

Get In Touch

I’m always looking for awesome input, feedback and critique!