Re: Use SQL to update PART of a field

On 08 Apr 2013 10:35, Alan Shore wrote:

Before I forget (like I normally do) we are on V5r4.
Within SQL, how can I update part of a field.
I think I can use the following
The field FULLFIELD in FileA is 20 characters, but I need to change
the 5th character to the value A for ALL records.
The SQL would be
Update FileA
set FULLFIELD =
substr(FULLFIELD, 1, 4) concat 'A' concat substr(FULLFIELD, 6, 15)

However, saying that, I thought that I had read somewhere, a
different method for changing the value of a portion of a field.
Obviously, I could have imagined this, but if anyone knows what I am
talking about, could they put me out of my misery.
As always, any answer gratefully accepted.

Sorry... I hit send on my prior reply without adding the example... and the link was not for v5r4. I think the INSERT scalar was added in v5r3; it is available on that release anyhow.

The following will change the fifth character to the letter 'A' in each selected occurrence of FullField from FileA:

update FileA
set FullField = INSERT(FullField, 5, 1, 'A')

http://publib.boulder.ibm.com/infocenter/iseries/v5r4/topic/db2/rbafzmst02.htm#ToC_495
"...
_i INSERT i_

>>-INSERT--(--source-string--,--start--,--length--,--insert-string--)-><

...

_length_

An expression that returns a built-in BIGINT, INTEGER, or SMALLINT data type. The integer specifies the number of characters that are to be deleted from source-string, starting at the character position identified by start. The value of the integer must be in the range of 0 to the length of source-string.
..."


To instead effect the /insert/ of the character 'A' into the fifth position; specify zero as the /length/ of characters to delete:

update FileA
set FullField = INSERT(FullField, 5, 0, 'A')