RE: SQL substring question

[Vernon Hamberg <vhamberg@xxxxxxxxxxx>]

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

The REPLACE function replaces all occurrences of search-string in 
source-string with replace-string. If search-string is not found in 
source-string, source-string is returned unchanged.

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.