UPDATETEXT Statement

Updates an existing text or image field. WRITETEXT updates and replaces an entire text or image field. UPDATETEXT is more flexible because it changes only a portion of a text or image value in place.

Syntax

UPDATETEXT
[[database.]owner.]table_name.dest_column_name dest_text_ptr
{NULL | insert_offset} {NULL | delete_length} [WITH LOG]
[inserted_data |
[{[database.]owner.]table_name.src_column_name src_text_ptr}]

where

table_name.dest_column_name

Specifies the table and text or image column to be updated. The database name and owner name are optional.

dest_text_ptr

Is a text pointer value (returned by the TEXTPTR function) that points to the text or image data to be updated.

insert_offset

Is the zero-based starting position, specified as the number of bytes (from the start of the existing text or image value) to skip before inserting the new data. The existing text or image data beginning at this zero-based starting position will be shifted to the right to make room for the new data. A value of 0 means that the new data will be inserted at the beginning of the existing data value. A value of NULL means that the new data will be appended to the existing data value.

delete_length

Is the number of bytes to delete from the existing text or image value, starting at the insert_offset position. A value of 0 means that no data will be deleted. A value of NULL means that all data from the insert_offset position to the end of the existing text or image value will be deleted.

WITH LOG

Specifies that the inserted text or image data will be logged. This option allows recovery, but it can quickly increase the size of the transaction log.

Note If the WITH LOG option is not specified, the database must have the select into/bulkcopy database option turned on. For details, see the sp_dboption system stored procedure.

inserted_data

Is the data to be inserted into the existing text or image value at the insert_offset location. This is a single char, varchar, binary, varbinary, text, or image constant.

table_name.src_column_name

Specifies the table and text or image column that can be used as the source of the inserted data.

src_text_ptr

Is a text pointer value (returned by the TEXTPTR function) that points to a text or image value that can be used as the source of the inserted data.

Remarks

The UPDATETEXT statement can be used to replace existing data, delete existing data, or insert new data. Newly inserted data can be a single inserted_data constant, or a table name, column name, or text pointer.

To replace data, specify a non-null insert_offset value, a non-zero delete_length value, and the new data to be inserted.

To delete existing data, specify a non-null insert_offset value and a non-zero delete_length. Do not specify any new data to be inserted.

To insert new data, specify the desired insert_offset value, a delete_length of 0, and the new data to be inserted.