Re[2]: Substring in SQL update

[Buck Calabro <mcalabro@xxxxxxxxxxxx>]

>>Is there a way to use substr when updating with SQL (at v2r3m0 -- we're
>>a tad behind the times yet)?  The manual tells how to select using
>>substr, but not to update or set..
>
>Yes of course, you can use the SQL Substr-Function in every UPDATE 
>statement, in the SET-clause on the _RIGHT_ side of the equal sign. That
>means, that you can move the Substr to any string field, but you could NOT
>use the Substr as the target (like in RPG/IV)!

We had a situation where we needed to replace the first 3 characters of a
field and leave the rest alone.

The field typically looks like this:
                        WI8N7F3A 
                        800N6F3  
        Want to exchange the 800 with WI8 and leave the rest of
        the field intact: Replace 800xxxxx with WI8xxxxx.

update ccextfp set ftrcod='WI8' concat substr (ftrcod,4,5)

You could follow a similar pattern if your firld is in the middle:

update ccextfp set ftrcod=substr(ftrcod,1,2) concat 
   '8' concat substr (ftrcod,4,5)

would replace the 3rd column and leave the rest intact.

hth
Buck Calabro
Commsoft

+---
| This is the Midrange System Mailing List!
| To submit a new message, send your mail to "MIDRANGE-L@midrange.com".
| To unsubscribe from this list send email to MAJORDOMO@midrange.com
|    and specify 'unsubscribe MIDRANGE-L' in the body of your message.
| Questions should be directed to the list owner/operator: david@midrange.com
+---