How to append a text field value to a database in SQL Server

How to append a text field value to a database in SQL ServerAppend and update the text field values. A simple update statement was used at the beginning of the operation. An error occurred,

In the past, fields of the text and ntext types cannot perform the add (+) operation with those of the varchar type. After searching for the information for half a day, I checked the help documentation and finally found a solution. Table: Create Table [DBO]. [Test] ([ID] [int] identity (1, 1) not null, [mytext] [text] collate chinese_prc_ci_as null) on [primary] textimage_on [primary] Go data: id mytext 1 AAAA

Append a string to the text type:Declare @ ptrval binary (16) Select @ ptrval = textptr (mytext) from testupdatetext test. mytext @ ptrval null 0 'bbbb' running result: Id mytext 1 aaaabbbb

The append is successful, which is completed in the database. Operations on text fields are different from operations on other string fields. Here, you must use the updatetext statement.

 The following is the help description of updatetext:Updatetext updates the existing text, ntext, or image fields.

Use updatetext to change a part of the text, ntext, or image column at an appropriate position.

Use writetext to update and replace the entire text, ntext, or image field. Syntax updatetext {includest_text_ptr} {null | insert_offset} {null | delete_length} [with log] [inserted_data | {table_name.src_column_name src_text_ptr}] parameter values: tables to be updated, text, ntext, or column name.

The table name and column name must comply with the identifier rules. For more information, see use identifiers. The specified database name and owner name are optional. The value of dest_text_ptr pointing to the text pointer of the text, ntext, or image data to be updated (returned by the textptr function ). Dest_text_ptr must be binary (16 ). Insert_offset is the starting position of a zero-based update. For text or image columns, insert_offset is the number of bytes to be skipped from the starting point of the existing column before new data is inserted. For ntext columns, insert_offset is the number of characters (each ntext character occupies 2 bytes ). The existing text, ntext, or image data starting from the zero-based start point shifts to the right to free up space for new data. If the value is 0, the new data is inserted at the beginning of the existing location. If the value is null, the new data is appended to the existing data value. Delete_length is the Data Length starting from insert_offset and to be deleted from the existing text, ntext, or image columns. The delete_length value is specified in bytes for the text and image columns and in characters for the ntext column. Each ntext character occupies 2 bytes. If the value is 0, data is not deleted. If the value is null, all data from insert_offset to the end of the existing text or image column is deleted. With log is ignored in Microsoft SQL Server 2000. In this version, log records are determined by the effective recovery model of the database. Inserted_data is the data to be inserted to the insert_offset position of the existing text, ntext, or image column. This is a single char, nchar, varchar, nvarchar, binary, varbinary, text, ntext, or image value. Inserted_data can be text or variable. Table_name.src_column_name is used as the name of the table, text, ntext, or image column to insert the data source. The table name and column name must comply with the identifier rules. Src_text_ptr points to the text pointer value of the text, ntext, or image Column Used as the data source to be inserted (returned by the textptr function ). Note that the newly inserted data can be a single inserted_data constant, table name, column name, or text pointer. The updatetext parameter of the update operation replaces the existing data to specify a non-empty insert_offset value, a non-zero delete_length value, and the new data to be inserted. Delete existing data to specify a non-empty insert_offset value and a non-zero delete_length value. Do not specify to insert