/*
search/replace script to replace characters in the content fields
of the content_text and content_teaser fields
 = *space*
€“ = —
€™ = '
€¢ = •
€¦ = :
*/

set xact_abort on

declare @otxt varchar(8000)
set @otxt = '' --string to find

declare @ntxt varchar(8000)
set @ntxt = '' --replacement string

declare @txtlen int
set @txtlen = len(@otxt)

declare @ptr binary(16)
declare @pos int
declare @id int

begin tran

-- begin content_html loop
while (select count(content_id) from content where content_html like '%' + @otxt +'%') > 0
begin

	declare curs cursor local fast_forward
	for
	select
		content_id,
		textptr(content_html),
		PATindex('%' + @otxt + '%', content_html)-1
	from
		content
	where
		content_html
	like
		'%' + @otxt +'%'

	open curs

	fetch next from curs into @id, @ptr, @pos

	while @@fetch_status = 0
	begin
		print 'Text found in row id=' + cast(@id as varchar) + ' at pos=' + cast(@pos as varchar)
		updatetext content.content_html @ptr @pos @txtlen @ntxt
		fetch next from curs into @id, @ptr, @pos
	end

	close curs
	deallocate curs
end

-- begin content_teaser loop
while (select count(content_id) from content where content_teaser like '%' + @otxt +'%') > 0
begin
	declare curs cursor local fast_forward
	for
	select
		content_id,
		textptr(content_teaser),
		PATindex('%' + @otxt + '%', content_teaser)-1
	from
		content
	where
		content_teaser
	like
		'%' + @otxt +'%'

	open curs
	fetch next from curs into @id, @ptr, @pos

	while @@fetch_status = 0
	begin
		print 'Text found in row id=' + cast(@id as varchar) + ' at pos=' + cast(@pos as varchar)
		updatetext content.content_teaser @ptr @pos @txtlen @ntxt
		fetch next from curs into @id, @ptr, @pos
	end

	close curs
	deallocate curs
end

commit tran
--rollback tran