Analog string handler function stuff processing Ntext field

function | string


if exists (SELECT * from dbo.sysobjects WHERE id = object_id (N ' [dbo].[ P_stuff] and OBJECTPROPERTY (ID, N ' isprocedure ') = 1) drop procedure [dbo]. [P_stuff] Go





/*--ntext Field Processing





analog String Handler stuff complete the stuff processing of ntext fields in a table note that a primary key (or identity field) with a column name: ID is required in the table, the data type is int if there is no such primary key field, or other type, the corresponding need modifies the stored procedure





--Jiangjian 2004.07--*/





/*--Call Example





--Test data CREATE TABLE TB (ID int identity (1,1), content ntext) Insert TB Select ' A;SD ' union ALL SELECT ' A;SDFKJAS2QASDF dfsg45yhjhdfg45645a '--invoke the stored procedure to replace the 8th to 9th character with ' China ' exec p_stuff ' TB ', ' content ', 8, 2, ' China ', ' SELECT * from TB drop table tb--*/





create proc p_stuff@tbname sysname--the table name to be processed @fdname sysname,--text/ntext field name @start int=null,--start position, NULL indicates append data @length Int=null,--Replacement length @str nvarchar (4000),--the string to insert @where nvarchar (1000) = '--the condition of the record to be processed Asif @str is null Returndeclare @s nvarchar (4000) Set @s= ' Declare @id int, @ptr varbinary (), @start1 int





declare TB cursor local for select Id,start=datalength ([' + @fdname + '])/2from [' + @tbname + '] ' +case isnull (@where, ') When ' then ' else ' where ' + @where end+ '





Open TB fetch TB into @id, @start1while @ @fetch_status =0begin Select @ptr =textptr (content) from [' + @tbname + '] where I d= @id





If @start is null or @start1 < @start updatetext [' + @tbname + ']. [' + @fdname + '] @ptr null null @str ELSE BEGIN set @start1 = @start-1 updatetext [' + @tbname + ']. [' + @fdname + '] @ptr @start1 @length @str end fetch TB into @id, @start1endclose tbdeallocate TB ' exec sp_executesql @s, N ' @st Art int, @length int, @str nvarchar (4000) ', @start, @length, @strgo