SQL Server modifies two scenarios for replacing Text,ntext type fields

Scenario one with update and replace

--Replacement statement (because the maximum value of varchar (max) is 8000, so a portion greater than 8000 is truncated)UPDATEDbo. Sns_userblogSET [Description]=REPLACE(CAST([Description]  as VARCHAR(Max)),'SX',"')WHEREBlogID=142

This scheme has some limitations, that is, the length of the field must not exceed 8000.

Scenario Two UpdateText method

--UPDATE statementDECLARE @ptrval BINARY( -)  SELECT @ptrval=TEXTPTR([Description]) fromSns_userblogWHEREBlogID=142IF @ptrval  is  not NULLUPDATETEXTSns_userblog.[Description] @ptrval NULL NULL 'Hellotheword SX'--Replacement statement (because the maximum value of varchar (max) is 8000, so a portion greater than 8000 is truncated)UPDATEDbo. Sns_userblogSET [Description]=REPLACE(CAST([Description]  as VARCHAR(Max)),'SX',"')WHEREBlogID=142

The method of UPDATETEXT parameter expansion in three-way scheme

--substitution StatementsDECLARE @ptr binary( -)--text PointerDECLARE @oldstr VARCHAR( -)Declare @dl INT--The length of the string that needs to be replacedDECLARE @val VARCHAR( -)--new string after replacementDECLARE @index INT --the start position to insertSet @oldstr = 'SX'--the string to replaceSet @dl = Len(@oldstr)--string block lengthSELECT @ptr=TEXTPTR([Description]),@val="',@index=CHARINDEX(@oldstr,[Description])-1  fromdbo. Sns_userblogWHEREBlogID=142--Query CriteriaUPDATETEXTDbo. Sns_userblog.description@ptr @index @dl @val

SQL Server modifies two scenarios for replacing Text,ntext type fields