Update statement with recursive function

General
N/A

Update statement with recursive function

Hi

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.

Eg:

Table having

COL1

thethe

there there

then the

it should be converted to

COL1

therethere

therere therere

theren there

3 REPLIES
N/A

Re: Update statement with recursive function

Okay guys I got how to get upto 2 levels

UPD DB.TableName
FROM
          (
            SEL
              Unchanged,
              CASE
              WHEN  Changed LIKE '%the%'
              THEN
                 SUBSTR(Changed,1, INDEX(Changed,'the')-1 ) || 'there.' || SUBSTR(Changed,INDEX(Changed,'the')+3 )
                 ELSE Changed
                 END AS DONE
            FROM
             (
                   SELECT 
                   ColumnName AS Unchanged
                   ,SUBSTRING(ColumnName FROM 1 FOR
                   POSITION('the' IN ColumnName)-1 ) ||'there'
                   ||SUBSTRING(ColumnName FROM POSITION('the' IN ColumnName)+3 ) AS Changed
                   FROM   DB.TableName
                   WHERE ColumnName IS NOT NULL --AND Changed LIKE '%the%'
                ) A
             ) B
SET ColumnName = DONE
WHERE Unchanged = ColumnName;

if you can update more that would be helpful

N/A

Re: Update statement with recursive function

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  :-)

Dieter

N/A

Re: Update statement with recursive function

Ha Ha thanks Dieter.