I am looking to update a Row like when ever it finds a word THE it should replace with THERE.
I tried to update using the BTEQ with repeat it was working fine but i need to mention how many times to repeat can you guys help me any other way. OREPLACE dosen't work in my environment.
it should be converted to
Okay guys I got how to get upto 2 levels
WHEN Changed LIKE '%the%'
SUBSTR(Changed,1, INDEX(Changed,'the')-1 ) || 'there.' || SUBSTR(Changed,INDEX(Changed,'the')+3 )
END AS DONE
ColumnName AS Unchanged
,SUBSTRING(ColumnName FROM 1 FOR
POSITION('the' IN ColumnName)-1 ) ||'there'
||SUBSTRING(ColumnName FROM POSITION('the' IN ColumnName)+3 ) AS Changed
WHERE ColumnName IS NOT NULL --AND Changed LIKE '%the%'
SET ColumnName = DONE
WHERE Unchanged = ColumnName;
if you can update more that would be helpful
This will not work because the 2nd update will also find the first 'the'.
If this is a one time update you can only replace 'the' with an expression which doesn't exist in ColumnName like '$$$$$' and then repeat it until activitycount = 0.
Then do another set of update and replace '$$$$$' with 'there'.
Or simply ask your DBA to install oREPLACE :-)