RE: How to use SQL to update a substring?

[<SRamanujan@xxxxxxxxxxxxxxxxxx>]

Mike,
The following works.  The t2city here is 10 char long so since we need
to update the entire field in a table in sql, this replaces the 1st char
with what ever we want and the rest is length - the position.  We can
manipulate this further

update file1 set t2city = 'A'||substring(t2city,2,9)

In your case, you might have to do:

update FILEXXX SET field1 =
substr(field1,1,5)||'2'||substr(field1,7,length-6)

HTH,
Thanks,
Sudha
Sudha Ramanujan
SunGard Futures Systems
sramanujan@xxxxxxxxxxxxxxxxxx
(312) 577 6179
(312) 577 6101 - Fax
 
 

-----Original Message-----
From: Gottlieb, Mike [mailto:MGottlieb@xxxxxxxxxxxxxx] 
Sent: Wednesday, March 16, 2005 9:56 AM
To: rpg400-l@xxxxxxxxxxxx
Subject: How to use SQL to update a substring?

I can't seem to use SQL to update a SUBSTR of a field.

I'm trying to do:

UPDATE FILEXXX SET SUBSTR(FIELD1,6,1) = '2'
SQL won't let me do it.

Are there any workarounds for this ?

TIA,
Mike


-- 
This is the RPG programming on the AS400 / iSeries (RPG400-L) mailing
list
To post a message email: RPG400-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/rpg400-l
or email: RPG400-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/rpg400-l.