Replace data of one table with data of other table

One of the questions asked in a forum was about replacing data of one table by the data of another table
The solution that the questioner used was having cursor by looping through the source table and replace particular words by other words from another table by matching the words

Here is my alternate solution

declare @Datatable table(Field1 nvarchar(50))

insert into @Datatable (Field1)
select 'testing for this' union all
select
'testing for this to'

 

declare @MatchTable table(LookFor nvarchar(50),Replacewith nvarchar(50))
insert into @MatchTable (LookFor,Replacewith)
select 'for','with' union all
select
'to','no'

declare @replace varchar(8000),@with varchar(8000),@sql varchar(8000)

select @replace='',@with ='',@sql=''

select
        @replace
=@replace +'replace(',
        @with =@with +''''+Lookfor +''','''+Replacewith+'''),'
from 
        @MatchTable

select @replace=@replace +'''~!@#'',',@with =SUBSTRING(@with,1,len(@with)-1)

select @sql=@sql+REPLACE('select '+@replace+@with,'~!@#',Field1)+' union all ' from @Datatable

select @sql=SUBSTRING(@sql,1,len(@sql)-10)

select * from @Datatable

exec(@sql)

 

Published Friday, January 8, 2010 11:26 AM by Madhivanan
Filed under: , ,

Comments

No Comments