Use substring in RPGLE SQL Statements

  • Home
  • /
  • Blog
  • /
  • Use substring in RPGLE SQL Statements

August 1, 2017

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. 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 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 don’t like it.

But it works and delivers the result I wanted.

So, I guess 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"}

Start your
7-day free trial

Take This Course with ALL ACCESS

Unlock your Learning Potential with instant access to every course and all new courses as they are released.
 [ For Serious Software Developers only ]

IBM i Training For Technology Experts


Successfully Work Remote 

 $ 129.00  $59.00

By adding new skills in information technology, employers will be confident that you have the necessary skills and tools needed to successfully work remotely


Snug CBD

 20% Discount

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