IBM i SQL – Using SUBST to insert data into a string 

 October 31, 2018

By  NickLitten

A while ago I had a problem where I wanted to change the first two characters in a table and I used the very simple concat and subst operations to do it with SQL. Today, we had a slightly more complex requirement of updating text in the middle of a field using SQL, or in other other words, squeezing some data into a field in a database.

Example: Imagine a flat file (aka table) has one big field (aka column) and we want to change two characters at position 69 & 70 dependent on certain conditions.

So, we are effectively squishing three things together — the beginning bit of data, our new characters and the end bit.

The trouble is, concat only allows two things to be squished together and since we need to update characters in the middle of a string -we are going to simply use concat twice:

update fileset
 set fieldA =
 concat( substr(fieldA,1,68) , concat('T1' , substr(fieldA,71)))
 where fieldB = 'stuff' and
 fieldC = 'otherstuff'

Works like a charm!





IBM i Software Developer, Digital Dad, AS400 Anarchist, RPG Modernizer, Trekkie, Nerd 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 in the words of the most interesting man in the world: Stay thirsty my friend.

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!