RE: Use SQL to update PART of a field

Thank you Alan, thank you Chuck!

-----Original Message-----
From: midrange-l-bounces@xxxxxxxxxxxx [mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of CRPence
Sent: Monday, April 08, 2013 12:10 PM
To: midrange-l@xxxxxxxxxxxx
Subject: 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')

--
Regards, Chuck
--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list To post a message email: MIDRANGE-L@xxxxxxxxxxxx To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx Before posting, please take a moment to review the archives at http://archive.midrange.com/midrange-l.