Re: SQL Update for part of a field.

the replace function...

Tommy.Holden@xxxxxxxxxxxxxxxxxxxxx wrote:

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




--
"Suppose you were an idiot...
And suppose you were a member of Congress...
But I repeat myself."
- Mark Twain

===========================================================
R Bruce Hoffman