From: Sharon Cannon To: Ted Holt

I know that with SQL it is very easy to update a field, but is it possible to substring part of a field and update that partial field?

From: Ted Holt To: Sharon Cannon

Certainly! You can use the SUBSTR operator to get a substring from a field. For example, to get bytes 3-7 of a field called MYNAME, use the expression SUBSTR(MYNAME,3,5).

You can update part of a character field by concatenating the parts you want to stay intact with the new value. Let's say you're going to replace the first character of a field called CITY with 'X' in file SOMEFILE. The following SQL statement could accomplish the task:

 UPDATE SOMEFILE SET CITY = 'X' CONCAT SUBSTR(CITY,2) WHERE ... 

It sure beats writing a quick-and-dirty program.

From: Sharon Cannon To: Ted Holt

I tried your suggestion and it worked perfectly! I wanted to use an SQL statement to update a flat (program-described) file containing one 80-byte field called DATA. I needed to change an amount field in positions 30 through 35 to contain all zeros. Here's the statement I used:

 UPDATE LMLIBR1/CREDITU SET DATA = SUBSTR(DATA,1,29) CONCAT '000000' CONCAT SUBSTR(DATA,36,45) 

It worked like magic!