home
Google



Search and replace in text columns
Author Nigel Rivett
This code will replace all occurrances of a string in a text column in all rows.
It also allows for the replaced text to be included in the replacing text.

this uses the following table to test

Create table test (i int, j int, t text)
go
create unique index ix on test
	(i, j)
go

it can be populated with test data as follows using the nr_AddTextRec stored procedure in this site

delete test
exec nr_AddTextRec 1, 1, 'asadf', 'I'
exec nr_AddTextRec 1, 1, 'jjjjj', 'A'
exec nr_AddTextRec 1, 1, 'kkkkksadf', 'A'
declare @t varchar(8000)
select @t = space(6000) + 'sadf'
exec nr_AddTextRec 1, 1, @t, 'A'
select @t = 'sadf'
exec nr_AddTextRec 1, 1, @t, 'A'
select @t = space(6000) + 'sadf'
exec nr_AddTextRec 1, 1, @t, 'A'
select @t = 'sadf'
exec nr_AddTextRec 1, 1, @t, 'A'

exec nr_AddTextRec 1, 2, 'asadf', 'I'
exec nr_AddTextRec 1, 2, 'jjjjj', 'A'
exec nr_AddTextRec 1, 2, 'kkkkksadf', 'A'
--declare @t varchar(8000)
select @t = space(6000) + 'sadf'
exec nr_AddTextRec 1, 2, @t, 'A'
select @t = 'sadf'
exec nr_AddTextRec 1, 2, @t, 'A'
select @t = space(6000) + 'sadf'
exec nr_AddTextRec 1, 2, @t, 'A'
select @t = 'sadf'
exec nr_AddTextRec 1, 2, @t, 'A'



The code to replace all occurrances of the following string in a column in all rows  


declare	@old varchar(20) ,
	@new varchar(20)
select 	@old = 'adf' ,
	@new = 'adfadf'	
	
-- 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 (i int, j int, Offset int, id int identity)
	select @id = 0
	
	-- get rid of old text
	while exists (select * from test where patindex('%' + @old + '%', t) <> 0)
	begin
		insert	#a (i, j, offset) select i, j, patindex('%' + @old + '%', t) - 1
		from	test where patindex('%' + @old + '%', t) <> 0
		
		while @id < (select max(id) from #a)
		begin
			select @id = @id + 1
			select 	@ptr = textptr(t) ,
				@offset = offset
			from	test t
				join #a
					on #a.i = t.i
					and #a.j = t.j
			where	#a.id = @id
			updatetext test.t @ptr @offset @dellen ''
		end
	end
	
	-- add new text
	while @id > 0
	begin
			select 	@ptr = textptr(t) ,
				@offset = offset
			from	test t
				join #a
					on #a.i = t.i
					and #a.j = t.j
			where	#a.id = @id
			updatetext test.t @ptr @offset 0 @new
			
			select @id = @id - 1
	end
drop table #a



home