set xact_abort on
begin tran

declare @otxt varchar(2000)
set @otxt = '/CMS/Workarea/showcontent.aspx' --string to find

declare @ntxt varchar(2000)
set @ntxt = '/Workarea/showcontent.aspx'		--replacement string

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

declare @link nvarchar(2000)
declare @pos int
declare @id int

declare curs cursor local fast_forward
for
select 
	lib_id,
	filename,
	PATindex('%' + @otxt + '%', filename)-1
from 
	library
where 
	filename
like 
	'%' + @otxt +'%'
and
	libtype_id = 4

open curs

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

while @@fetch_status = 0
begin
	print 'Text found in row id=' + cast(@id as varchar) + ' at pos=' + cast(@pos as varchar)
	update library set filename = left(@link, @pos) + @ntxt + right(@link, len(@link) - @pos - @txtlen) where lib_id = @id
	
fetch next from curs into @id, @link, @pos	
end

close curs
deallocate curs

commit tran