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 Processing Functions The stuff completion of the NTEXT field in the table, you need to have a primary key (or identification field) that is column name: ID, the data type is int if there is no primary key field, or other types, Need to modify the stored procedure
- Zou Jian 2004.07 (Please keep this information) - * /
/ * - Call example
- Test Data Create Table TB (ID Int Id Id Id Id IDENTITY (1), Content NText) Insert Tb Select 'A; SD' Union All Select 'A; SDFKJAS2QASDFDFSG45YHJHDFG45645A' - Call the stored procedure, replace the characters of No. 8 ~ 9 Cheng 'China' EXEC P_STUFF 'TB', 'Content', 8, 2, 'China', 'SELECT * FROM TB DROP TABLE TB - * /
CREATE P_STUFF @ TBNAME SYSNAME, - To process the table name @fdname sysname, --text / ntext field name @start int = null, - Start position, NULL indicates adding data @Length Int = null, - replacement Length @str nvarchar (4000), - String @where nvarchar (1000) = '- Conditions to be processed Asif @Str Is Null Returndeclare @s nvarchar (4000) SET @ s =' declare @ID int, @ ptr varbinary (16), @ start1 int
DECLARE TB CURSOR LOCAL for SELECT ID, START = DATALENGTH ([' @ fdname ']) / 2FROM [' @ TBNAME '] ' Case Isnull (@where,' ') when' 'Ten' Else 'Where' @ WHERE END '
Open tb fetch tb @ ID, @ Start1While @@ fetch_status = 0begin select @ PTR = Textptr (Content) from [' @ TBNAME '] where id = @ id