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:

Swap Homes and Travel the World – Join Home Exchange
Surf the Web Securely with OPEN DNS
IBM i Software Change Management – CMS vs ALM – What’s in a Name?
Want to learn CL Programming?
{"email":"Email address invalid","url":"Website address invalid","required":"Required field missing"}

Get In Touch

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


Snug CBD

 20% Discount

I have partnered with SNUG CBD givING you Organic CBD
20% discount code "NL20"