home
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