First, the problem description:
1. In SQL Server, the Replace function is not allowed to be replaced by the Ntext/text/image field;
2. With convert field conversion, you can convert the ntext field to varchar (8000) and replace it with the Relpace function, however, this method is not valid for ntext fields that have a field length greater than 8000.
Ii. Problem-solving
To organize the common stored procedures, the code is as follows:
Copy Code code as follows:
CREATE procedure [dbo]. [Proc_updatentextfield]
@TargetTable nvarchar (1000),--Target table name
@TargetField nvarchar (1000),--Target Field name
@PKField nvarchar (1000),--The Table primary key field name
@otxt nvarchar (1000)--strings to be replaced
@ntxt nvarchar (1000)--replaced string
As
Begin
declare @SqlStr nvarchar (4000)
Set @SqlStr = ' Declare @txtlen int '
Set @SqlStr = @SqlStr + ' Set @txtlen = Len (' + @otxt + ') '
Set @SqlStr = @SqlStr + ' Declare @pos int '
Set @SqlStr = @SqlStr + ' Set @pos = 0 '
Set @SqlStr = @SqlStr + ' Declare curs cursor local fast_forward for select '
Set @SqlStr = @SqlStr + @PKField + ', textptr (' + @TargetField + ') from ' + @TargetTable + ' where ' + @TargetField + ' Li Ke '% ' + @otxt + '% '
Set @SqlStr = @SqlStr + ' Declare @ptr binary (16) '
Set @SqlStr = @SqlStr + ' Declare @id char (32) '
Set @SqlStr = @SqlStr + ' Open curs '
Set @SqlStr = @SqlStr + ' fetch next from curs into @id, @ptr '
Set @SqlStr = @SqlStr + ' while @ @fetch_status = 0 '
Set @SqlStr = @SqlStr + ' begin '
Set @SqlStr = @SqlStr + ' Select @pos = patindex ('% ' + @otxt + '% ', productdesc) from producttemp where productid= @id '
Set @SqlStr = @SqlStr + ' while @pos >0 '
Set @SqlStr = @SqlStr + ' begin '
Set @SqlStr = @SqlStr + ' Set @pos = @pos-1 '
Set @SqlStr = @SqlStr + ' updatetext ' + @TargetTable + '. ' + @TargetField + ' @ptr @pos ' + @txtlen + ' '
Set @SqlStr = @SqlStr + ' Select @pos = patindex ('% ' + @otxt + '% ', productdesc) from producttemp where productid= @id '
Set @SqlStr = @SqlStr + ' End '
Set @SqlStr = @SqlStr + ' fetch next from curs into @id, @ptr '
Set @SqlStr = @SqlStr + ' End '
Set @SqlStr = @SqlStr + ' Close curs '
Set @SqlStr = @SqlStr + ' deallocate curs '
EXECUTE sp_executesql @SqlStr
End
