RE: SQL substring question

They have - it is called REPLACE. Available as of V5R3.

HTH
Vern

At 12:41 PM 1/17/2007, you wrote:

Wonder if they will ever make it so you can just update a substring...


Thanks,
Tommy Holden


-----Original Message-----
From: rpg400-l-bounces@xxxxxxxxxxxx
[mailto:rpg400-l-bounces@xxxxxxxxxxxx] On Behalf Of rob@xxxxxxxxx
Sent: Wednesday, January 17, 2007 12:30 PM
To: RPG programming on the AS400 / iSeries
Subject: Re: SQL substring question

update myfile
set field2=substr(field2,1,88) concat 'XX' concat substr(field2,91,152)
where substr(field2,89,2)='AA'
  and substr(field1,1,3)='TRD'

Rob Berendt
--
Group Dekko Services, LLC
Dept 01.073
PO Box 2000
Dock 108
6928N 400E
Kendallville, IN 46755
http://www.dekko.com





"Rowe, Sheri" <srowe@xxxxxxxxx>
Sent by: rpg400-l-bounces+rob=dekko.com@xxxxxxxxxxxx
01/17/2007 01:07 PM
Please respond to
RPG programming on the AS400 / iSeries <rpg400-l@xxxxxxxxxxxx>


To
<rpg400-l@xxxxxxxxxxxx>
cc

Subject
SQL substring question






Hi,



I need to write a 'quick' interactive SQL statement to update a file
based on the conditional field being a substring and the result field
being a substring.



FileA

Field1 (7,A)   position 1,3 contains many values, but I need only 'TRD'
records and position 4-7 contains a customer number value  e.g.
TRD1100, TRD5555, TRD6789 etc



Field2 (243,A)  contains many values, but I need to extract positions
89-90



I need to update all Field1 'TRD' records where field2 position 89-90 =
'AA' to be value 'XX'



Any help would be very appreciated.



Thanks in advance.



Sheri Rowe

--
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.


--
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.


--
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.