Troublesome. Text Field

Many other fields cannot be used. Only the following functions and statements that support text field processing can be used with ntext, text, or image data. This is troublesome, these differences are listed here for future use.
Function statement
Datalength readtext
Patindex set textsize
Substring updatetext
Textptr writetext
Textvalid

1: replace

-- Create a data test environment
Create Table # Tb (AA text)
Insert into # TB select 'abc123abc123, ASD'

-- Define the replaced string
Declare @ s_str varchar (8000), @ d_str varchar (8000)
Select @ s_str = '20160901' -- string to be replaced
, @ D_str = '000' -- string to be replaced

-- String replacement
Declare @ P varbinary (16), @ postion int, @ rplen int
Select @ P = textptr (AA), @ rplen = Len (@ s_str), @ postion = charindex (@ s_str, AA)-1 from # TB
While @ postion> 0
Begin
Updatetext # TB. aa @ P @ postion @ rplen @ d_str
Select @ postion = charindex (@ s_str, AA)-1 from # TB
End

-- Display Results
Select * from # TB

-- Delete the data test environment
Drop table # TB

********************* ***/
Declare @ ptrval binary (16)
Select @ ptrval = textptr (AA) from # TB where AA like '% data 100'
If @ ptrval is not null -- Be sure to add this sentence; otherwise, an error will be reported if no data is found.
Updatetext # TB. aa @ ptrval 0 null 'data 3'

/**************** Add ******************* ***************/
-- Define the added string
Declare @ s_str varchar (8000)
Select @ s_str = '* C' -- string to be added
-- String addition Processing
Declare @ P varbinary (16), @ postion int, @ rplen int
Select @ P = textptr (detail) from test where id = '001'
Updatetext test. Detail @ P null @ s_str

Summary:
1: The text field type cannot be directly replaced by the replace function. updatetext must be used.
2: The where field = 'data' cannot be used for field comparison. You can use like instead.
3: When updatetext is used, an error occurs if @ ptrval is null.