quote:

---

Originally posted by webfred

This?
update Datatable
set Field1=replace(Field1,(select LookFor from MatchTable),(select Replacewith from MatchTable))

---

No, you're never too old to Yak'n'Roll if you're too young to die.

---

quote:

---

Originally posted by miketl

Looking for an efficient way to do a partial search and replace on a field in one table with a data in another field

A simplified table structure:

Datatable
Field1 nvarchar(50)


MatchTable
LookFor nvarchar(50)
Replacewith nvarchar(50)

Idea is to see if the value in lookfor exists (even partially) in Field1 for all rows in datatable and if it does replace it with the value from replacewith

I realize this can be done with a cursor and looping through each row in matchTable to see if lookfor exists in field1 in datatable but was hoping their might be a more efficient way someone smarter than I knows...

Purpose of the code is a bulk import routine that needs to normalise some data.. e.g things like Avenue to Ave

---

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)

quote:

---

Originally posted by webfred

Madhi the sorcerer!
What does '~!@#'?

---

No, you're never too old to Yak'n'Roll if you're too young to die.

---

quote:

---

Originally posted by miketl

Thanks madhivanan, not something I would have ever come up with.. looks the goods.. I will do some testing on it for load, but I think it will be about as fast as it can be done given what is required

---