Re: Use SQL to update PART of a field

[Charles Wilt <charles.wilt@xxxxxxxxx>]

Think you might be getting RPG's %subst confused with SQL...

in RPG %subst() can be on either side of the equals...

%subst(fullfield:5:1) = 'A';

Charles



On Mon, Apr 8, 2013 at 1:35 PM, Alan Shore <ashore@xxxxxxxx> wrote:

> Hi folks
> 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 answere gratefully accepted.
>
> Alan Shore
> Programmer/Analyst, Direct Response
> E:AShore@xxxxxxxx
> P:(631) 200-5019
> C:(631) 880-8640
> "If you're going through Hell, keep going" - Winston Churchill
>
>
> Disclaimer: This message contains confidential information and is intended
> only for the individual named. If you are not the named addressee you
> should not disseminate, distribute or copy this e-mail. Please notify the
> sender immediately by e-mail if you have received this e-mail by mistake
> and delete this e-mail from your system. E-mail transmission cannot be
> guaranteed to be secure or error-free as information could be intercepted,
> corrupted, lost, destroyed, arrive late or incomplete, or contain viruses.
> The sender therefore does not accept liability for any errors or omissions
> in the contents of this message, which arise as a result of e-mail
> transmission. If verification is required please request a hard-copy
> version. Any views or opinions presented are solely those of the author and
> do not necessarily represent those of the company.
> --
> 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.