Binaryworld - Search/Replace text in text, ntext datatype column ... [ SQL Server -> BLOB (Text-Image) ]

In SQL Server anything text longer than 8000 characters can be stored in TEXT or NTEXT datatype column but when you use TEXT or NTEXT datatype is not same as regular char, varchar, nchar or nvarchar datatype. You can not use string function like ltrim, rtrim, replace etc... Then how do you search and replace ????

Here is the stored proc which will replace specified text with new text.

Click here to copy the following block

/*
Copyright © 2005 Nayan Patel. All rights reserved.

Author   : NPatel
Created On : 8/15/05
Site    : www.binaryworld.net

Description : You can not use replace() function to search and replace in text/ntext/image field.
  This SP will help you to perform search and replace

-- drop table test

create table test(
myid int
,memo text
)

insert into test values (1,'aaa')
insert into test values (2,'bbb')

Print 'Before replace'
select * from test

exec usp_SearchReplaceText 'aaa','new_aaa','test','memo'

select * from test
Print 'After replace'

*/

create proc usp_SearchReplaceText
@str_find varchar(8000)
,@str_replace varchar(8000)
,@table_name varchar(128)
,@text_col varchar(128)
as
declare @cmd varchar(8000)

--//in sql 2000 textptr must be used in transaction if "text in row" option is enabled using
--//to enable "text in row option" use
--//EXEC sp_tableoption 'mytable', 'text in row', 'on'

set @cmd=
'
set xact_abort on
begin tran

declare @str_find varchar(1000)
set @str_find = ''' + replace(@str_find,'''','''''') + '''

declare @str_replace varchar(1000)
set @str_replace = ''' + replace(@str_replace,'''','''''') + '''

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

declare @ptr binary(16)
declare @pos int

declare curs cursor local fast_forward
for
select
   textptr(' + @text_col + '),
   charindex(@str_find, ' + @text_col + ')-1
from ' + @table_name + '
where ' + @text_col + ' like ''%'' + @str_find +''%''

open curs

fetch next from curs into @ptr, @pos

while @@fetch_status = 0
begin
   updatetext ' + @table_name + '.' + @text_col + ' @ptr @pos @txtlen @str_replace
   fetch next from curs into @ptr, @pos
end

close curs
deallocate curs

commit tran
'

execute(@cmd)
go

example

Click here to copy the following block

--drop table test
--go

create table test(
myid int
,memo text
)
go

insert into test values (1,'aaa')
insert into test values (2,'bbb')
go

Print 'Before replace'
select * from test

go

exec usp_SearchReplaceText 'aaa','new_aaa','test','memo'

go

Print 'After replace'
select * from test

Output

Before replace

myid        memo   
----------- -------
1           aaa
2           bbb


After replace

myid        memo    
----------- --------
1           new_aaa
2           bbb