![]() |
|
![]() |
![]() |
Archives of the TeradataForumMessage Posted: Mon, 02 Jul 2012 @ 19:34:21 GMT
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
| ||||||||||||||||||||||||||||||||||||||||||||||
| ||||||||||||||||||||||||||||||||||||||||||||||||
![]() | ||||||||||||||||||||||||||||||||||||||||||||||||
Copyright 2016 - All Rights Reserved | ||||||||||||||||||||||||||||||||||||||||||||||||
Last Modified: 15 Jun 2023 | ||||||||||||||||||||||||||||||||||||||||||||||||