Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums  SQL Server 2005 Forums  Transact-SQL (2005)  Problem with % character in search and replace

Author  Topic  roco Starting Member 9 Posts Posted - 2009-12-09 : 04:17:08 Hello! I have a problem with trying to loop through all rows searching and replacing all occurences of a string starting with the character '%'. The function I am using replaces all occurences but leaves the '%'. I have tried to use escape character but havent got it to work inside my "iterating" query. Hopefully somebody can give me a hint. Thanks! The code below replaces %20Test with %Test instead of just Test. declare @old varchar(20) , @new varchar(20) select @old = '%20Test', @new = 'Test' -- pk for table declare @i int , @j int declare @ptr binary(16) , @offset int , @dellen int , @id int select @dellen = len(@old) create table #a (LayoutID int, Offset int, id int identity) select @id = 0 -- get rid of old text while exists (select * from #LayoutTemp where patindex('%' + @old + '%', LayoutContent) <> 0) begin insert #a (LayoutID, offset) select LayoutID, patindex('%' + @old + '%', LayoutContent) - 1 from #LayoutTemp where patindex('%' + @old + '%', LayoutContent) <> 0 while @id < (select max(id) from #a) begin select @id = @id + 1 select @ptr = textptr(LayoutContent) , @offset = offset from #LayoutTemp t join #a on #a.LayoutID = t.LayoutID where #a.id = @id updatetext #LayoutTemp.LayoutContent @ptr @offset @dellen '' end end -- add new text while @id > 0 begin select @ptr = textptr(LayoutContent) , @offset = offset from #LayoutTemp t join #a on #a.LayoutID = t.LayoutID where #a.id = @id updatetext #LayoutTemp.LayoutContent @ptr @offset 0 @new select @id = @id - 1 end drop table #a