針對text類型的字符串替換：

設有表 T(id int not null,info text)
要求替換info中的'abc'為'123'
一般的存儲過程會寫成：
drop procedure dbo.procedure_1
go
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
create procedure dbo.procedure_1
as
declare @ptr varbinary(16)
declare @ID int
declare @Position int,@len int
declare @strsrc char(3)
declare @strdsc char(3)
set @strtmp='abc'
set @strdsc='123'
set @len=3
declare replace_Cursor scroll Cursor
for
select textptr([info]),id from T
for read only
open replace_Cursor
fetch next from replace_Cursor into @ptr,@ID
while @@fetch_status=0
begin
    select @Position=patindex('%'+@strsrc+'%',[info]) from T where id=@ID
    while @Position>0
    begin
        set @Position=@Position-1
        updatetext T.[info] @ptr @Position @len @strdsc
      select @Position=patindex('%'+@strsrc+'%',[info]) from T where id=@ID
    end
    fetch next from replace_Cursor into @ptr,@ID
end
close replace_Cursor
deallocate replace_Cursor
go

其中用到了text專用的函數(shù) updatetext

現(xiàn)在我們改寫成
drop procedure dbo.procedure_1
go

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

create procedure dbo.procedure_1
as

declare @ID int
declare @strtmp varchar(max)
declare @strsrc char(3),@strdsc char(3)
set @strsrc = 'abc'
set @strdsc = '123'
declare replace_Cursor scroll Cursor
for
select id from testtable
--for read only
open replace_Cursor
fetch next from replace_Cursor into @ID
while @@fetch_status=0
begin
    select @strtmp = [info] from testtable where id=@ID
    select @strtmp = Replace(@strtmp,@strsrc,@strdsc)
    update T set [info] = @strtmp where id=@ID
    fetch next from replace_Cursor into @ID
end
close replace_Cursor
deallocate replace_Cursor
go

這樣，無論info字段改成char,nchar,text都好，一樣均可通用