Home > IT Answers > AS/400 > Using AS400 SQL to replace a variable string
CharlieBrowne
32,865 pts.
 Using AS400 SQL to replace a variable string
AS/400, AS/400 SQL, SQL
I need to do a scan and replace in a flat file. File has one field 80 characters long. Possibly one or more of the records in the file contain a specific value, such as '%ABC%' and I want to update the record replace ABC with XYZ. This value could be anywhere within the record. I do not want to take the time to do an RPG program. Prefer to use RUNSQLSTM. Any ideas?

Software/Hardware used:
AS400 V5R4
ASKED: September 30, 2009  7:01 PM
UPDATED: October 28, 2009  4:10 PM
RELATED QUESTIONS
Answer Wiki:
In v6r1 and maybe before you can use REPLACE function update Myfile set MyField = replace(Myfield,'ABC','XYZ') but use care - this will replace every ABC with XYZ even the second or third occurance on the same record. I recommend that you view the data first Select Myfield from MyFile where MyField like ('%ABC%') Phil
Last Wiki Answer Submitted:  September 30, 2009  8:52 pm  by  philpl1jb   44,180 pts.
All Answer Wiki Contributors:  philpl1jb   44,180 pts.
To see all answers submitted to the Answer Wiki: View Answer History.


RSS - Discussions Help
Discuss This Question:
_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _

 

Thanks Phil
Worked perfert.
I had tried REPLACE but had the wrong syntax.

CharlieBrowne
 32,865 pts.
 

how about v5r2? can i do it?

JerryHandler
 10 pts.
 

You can do it in V5R4.

Sloopy
 2,195 pts.