Home Page for the TeradataForum
 

Archives of the TeradataForum

Message Posted: Mon, 02 Jul 2012 @ 19:34:21 GMT


     
  <Prev Next>  
<<First
<Prev
Next> Last>>  


Subj:   Procedure for updating the part of the string
 
From:   Verma, Pawan

Hi all,

I want to write a procedure to update a part of the string in the column.

Ex:

suppose a column(name Querytext) contains a query

     'SEL * from databasename_prototype. abc_prototype where xyz = 1'

now I want to change the databasename to new databasename in this column of the table.

below is the update query for this:

     UPD tablename FROM
     ((
     SELECT QueryText
     ,SUBSTRING(QueryText FROM 1 FOR
     POSITION('from databasename_prototype' IN QueryText)+4)
     ||'New_database_name'
     ||SUBSTRING(QueryText FROM POSITION('prototype' IN QueryText)+9 )
     FROM tablename ) dt (col1, col2) )
     SET QueryText = col2
     WHERE col1 = QueryText;

This query is working good. Now the case is if i have more than 2 instance for this change. then i have to write two selects but i want to put this in loop using SP so that only 1 select can update all the instances. below is the SP:

     REPLACE PROCEDURE update_part_string
     ( IN tb_replaced VARCHAR(40)
     , IN replace_with VARCHAR(40)
     , IN tb_replaced_last_word VARCHAR(20))
     BEGIN
     DECLARE pos_1 INTEGER;
     DECLARE pos_2 INTEGER;
     SELECT(( POSITION (' ' IN ':tb_replaced'))-1) INTO :pos_1;
     SELECT( CHARACTERS(:tb_replaced_last_word)) INTO :pos_2;

     UPDATE tablename
     FROM
     ((
     SELECT QueryText
     ,SUBSTRING(QueryText FROM 1 FOR
     POSITION(':tb_replaced' IN QueryText)+:pos_1) ||':replace_with'
     ||SUBSTRING(QueryText FROM POSITION(':tb_replaced_last_word' IN QueryText)+:
     pos_2 )
     FROM tablename) dt (col1, col2) )
     SET QueryText = col2
     WHERE col1 = QueryText;

     END;


     CALL update_part_string('from databasename_prototype' , 'New_database_name', 'prototype' )

There is something which i'm missing in the procedure and also want to know how to loop it.


Thanks,

Pawan



     
  <Prev Next>  
<<First
<Prev
Next> Last>>  
 
 
 
 
 
 
 
 
  
  Top Home Privacy Feedback  
 
 
Copyright for the TeradataForum (TDATA-L), Manta BlueSky    
Copyright 2016 - All Rights Reserved    
Last Modified: 27 Dec 2016