CREATE PROCEDURE sp_textreplace
	@table_name varchar (30),
	@field_name varchar (30),
	@key_name varchar (30),
	@otxt varchar (1000),
	@ntxt varchar (1000)

AS

begin tran

declare @txtlen int
set @txtlen = len(@otxt)
declare @txtlenchar as varchar(30)
set @txtlenchar=Cast(@txtlen as varchar(30))

exec('declare curs cursor 
for
select ' +
	@key_name + ', 
	textptr(' + @field_name + ')
from ' +
	@table_name + '
where ' +
	@field_name + '
like 
	''%' + @otxt + '%''')

open curs

exec('

declare @key int 
declare @pos int 
declare @ptr binary(16) 
declare @nextpos int

fetch next from curs into @key, @ptr
while @@fetch_status = 0
	begin
		set @nextpos=1
		select @pos=charindex(''' + @otxt + ''', ' + @field_name + ', @nextpos) - 1
			FROM ' + @table_name + ' WHERE ' + @key_name + ' = Cast(@key as Varchar(30))

		WHILE @pos>0
			BEGIN
				updatetext ' + @table_name + '.' + @field_name + ' @ptr @pos ' + @txtlenchar + ' ''' + @ntxt + '''
			
				SET @nextpos=@pos + ' + @txtlenchar + ' + 1

				select @pos=charindex(''' + @otxt + ''', ' + @field_name + ', @nextpos) -1
					FROM ' + @table_name + ' WHERE ' + @key_name + ' = Cast(@key as Varchar(30))
			END
		fetch next from curs into @key, @ptr
	end

')

close curs
deallocate curs

commit tran

quote:

---

Originally posted by javamick

Davide, this is a great idea! I've tried this and written my own stored procedure based on your code, but it seems that this part

where
TargetField
like
'%' + @otxt +'%'

does not work paste 8000 or so chars. Any idea on how to get around this?

---

quote:

---

Originally posted by nr

see
http://www.mindsdoor.net/SQLTsql/ReplaceText.html


==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

---

quote:

---

Originally posted by fredclown

If you use SQL 2005 you can use replace with a text type. All you have to do is the below ...

field = replace(cast(field as varchar(max)),'string' ,'replacement')

Easy as pie.

---