set xact_abort on

declare @currurl nvarchar(500)
declare @newurl nvarchar(500)
declare @search nvarchar(500)
declare @replace varchar(500)

set @search = '' --string to find
set @replace = ''		--replacement string

declare @pos int
declare @id bigint
declare @ol int
declare @lang int

begin tran
	
declare curs cursor local fast_forward
for
	select FolderId,Url,OrderLocation,ContentLanguage
	from sitemap 
	where url like '%' + @search +'%'
open curs
fetch next from curs into @id,@currurl,@ol,@lang
while @@fetch_status = 0
begin
	--print 'Url before update=' + @currurl
	Set @newurl = replace(@currurl,@search,@replace)
	Update SiteMap Set Url = @newurl 
	where folderid = @id and orderlocation = @ol and contentlanguage = @lang
	--print 'Url after update=' + @newurl

fetch next from curs into @id,@currurl,@ol,@lang
end

close curs
deallocate curs

--rollback tran
commit tran