Processing ntext, text, or image data in SQL

Processing of ntext, text, or image data in SQL -- adding and processing of text fields

-- Create a test table
Create Table Test (ID varchar (3), detail text)
Insert into test
Select '001', 'a * B'

-- Define the added string
Declare @ s_str varchar (8000), @ postion int
Select @ s_str = '* C' -- string to be added
, @ Postion = NULL -- position of the append. Add null to the end. Add 0 to the header. Add other values to the specified position.

-- String addition Processing
Declare @ P varbinary (16)
Select @ P = textptr (detail) from test where id = '001'
Updatetext test. Detail @ P @ postion 0 @ s_str

-- Display the processing result
Select * from test
Go

-- Delete the test table
Drop Table Test

-- Text field replacement

-- Create a data test environment
Create Table Test (ID varchar (3), TXT text)
Insert into test
Select '001', 'a * B'
Go

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

-- String replacement
Declare @ P varbinary (16), @ postion int, @ rplen int
Select @ P = textptr (txt)
, @ Rplen = Len (@ s_str)
, @ Postion = charindex (@ s_str, txt)-1
From test where id = '001'

While @ postion> 0
Begin
Updatetext test.txt @ P @ postion @ rplen @ d_str
Select @ postion = charindex (@ s_str, txt)-1 from test
End

-- Display Results
Select * from test

Go
-- Delete the data test environment
Drop Table Test

-- Storage process for adding text fields -- full table

-- Create a test table
Create Table [user] (UID int, userlog text)
Create Table [order] (UID int, State bit)

Insert into [user]
Select 1, 'A'
Union all select 2, 'B'
Union all select 3, 'C'

Insert into [order]
Select 1, 1
Union all select 2, 0
Union all select 3, 1
Go

-- Stored Procedure
Create procedure spupdateuserlog
@ Strlog text,
@ State int
As
-- Define a cursor to process data cyclically
Declare @ uid int
Declare # TB cursor for select a. uid from [user] A join [order] B on A. uid = B. uid
Where State = @ State

Open # TB
Fetch next from # TB into @ uid
While @ fetch_status = 0
Begin
-- String addition Processing
Declare @ P varbinary (16)
Select @ P = textptr (userlog) from [user] Where uid = @ uid
Updatetext [user]. userlog @ P null 0 @ strlog
Fetch next from # TB into @ uid
End
Close # TB
Deallocate # TB
Go

-- Call example:
Exec spupdateuserlog '123', 1

-- Display the processing result
Select * from [user]

Go

-- Delete the test environment
Drop table [user], [order]
Drop proc spupdateuserlog

/* -- Test Result

UID userlog
---------------------
1 A123
2 B
3 c123

(The number of affected rows is 3)
--*/
-- Text field replacement -- full table replacement

-- Create a data test environment
Create Table Test (ID varchar (3), TXT text)
Insert into test
Select '001', 'a * B'
Union all select '002 ', 'a * B-AA * BB'
Go

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

-- Define a cursor to process data cyclically
Declare @ ID varchar (3)
Declare # TB cursor for select ID from test
Open # TB
Fetch next from # TB into @ ID
While @ fetch_status = 0
Begin
-- String replacement
Declare @ P varbinary (16), @ postion int, @ rplen int
Select @ P = textptr (txt)
, @ Rplen = Len (@ s_str)
, @ Postion = charindex (@ s_str, txt)-1
From test where id = @ ID

While @ postion> 0
Begin
Updatetext test.txt @ P @ postion @ rplen @ d_str
Select @ postion = charindex (@ s_str, txt)-1 from test where id = @ ID
End

Fetch next from # TB into @ ID
End
Close # TB
Deallocate # TB

-- Display Results
Select * from test

Go
-- Delete the data test environment
Drop Table Test

************************
Only text fields are supported:
The following functions and statements can be used with ntext, text, or image data.
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.
*/