Archives of the TeradataForum

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

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

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