RE: SQL Update for part of a field.

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