RE: SQL Update for part of a field.

[Tommy.Holden@xxxxxxxxxxxxxxxxxxxxx]

i *think* i read somewhere that updating a portion of a field is possible 
in v5r4...of course i'm getting older and it could be just wishful 
thinking...

Thanks,
Tommy Holden



"Joe Pluta" <joepluta@xxxxxxxxxxxxxxxxx> 
Sent by: midrange-l-bounces@xxxxxxxxxxxx
06/26/2007 12:29 PM
Please respond to
Midrange Systems Technical Discussion <midrange-l@xxxxxxxxxxxx>


To
"'Midrange Systems Technical Discussion'" <midrange-l@xxxxxxxxxxxx>
cc

Subject
RE: SQL Update for part of a field.






> From: Jim Essinger
>
> Ok SQL gurus,
>
> I have a field that is very long, but I only want to update a part of 
the
> field.  The statment I have is this;
>
> update libname/filename
>   set substr(fieldname,2,3) = '225'
>  where substr(fieldname,1,1) in ('5', '8')

SQL doesn't allow updating a portion of a field.  Basically you have to
build the entire replacement string from snippets of the original field.

update libname/filename
  set fieldname = 
    substr(fieldname, 1, 1) concat
    '225' concat
    substr(fieldname, 5, nnn)
  where substr(fieldname,1,1) in ('5', '8')


Where nnn is the length of the substring minus four.

Joe