UPDATETEXT (T-SQL)

Updates an existing text, ntext, or image field. Use UPDATETEXT to change only a portion of a text, ntext, or image column in place. Use WRITETEXT to update and replace an entire text, ntext, or image field.

Syntax

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

Arguments

table_name.dest_column_name

Is the name of the table and text, ntext, or image column to be updated. Table names and column names must conform to the rules for identifiers. For more information, see Using Identifiers. Specifying the database name and owner names is optional.

dest_text_ptr

Is a text pointer value (returned by the TEXTPTR function) that points to the text, ntext, or image data to be updated. dest_text_ptr must be binary(16).

insert_offset

Is the zero-based starting position for the update. For text or image columns, insert_offset is the number of bytes to skip from the start of the existing column before inserting new data. For ntext columns, insert_offset is the number of characters (each ntext character uses 2 bytes). The existing text, ntext, or image data beginning at this zero-based starting position is shifted to the right to make room for the new data. A value of 0 inserts the new data at the beginning of the existing data. A value of NULL appends the new data to the existing data value.

delete_length

Is the length of data to delete from the existing text, ntext, or image column, starting at the insert_offset position. The delete_length value is specified in bytes for text and image columns and in characters for ntext columns. Each ntext character uses 2 bytes. A value of 0 deletes no data. A value of NULL deletes all data from the insert_offset position to the end of the existing text or image column.

WITH LOG

Specifies that the inserted text, ntext, or image data is 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 more information, see sp_dboption and Setting Database Options.

---

inserted_data

Is the data to be inserted into the existing text, ntext, or image column at the insert_offset location. This is a single char, nchar, varchar, nvarchar, binary, varbinary, text, ntext, or image value. inserted_data can be a literal or a variable.

table_name.src_column_name

Is the name of the table and text, ntext, or image column used as the source of the inserted data. Table names and column names must conform to the rules for identifiers.

src_text_ptr

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

Remarks

Newly inserted data can be a single inserted_data constant, table name, column name, or text pointer.

To initialize text columns to NULL, use UPDATETEXT when the compatibility level is equal to 65. If the compatibility level is equal to 70, use WRITETEXT to initialize text columns to NULL; otherwise, UPDATETEXT initializes text columns to an empty string. For information about setting the compatibility level, see sp_dbcmptlevel.

Permissions

UPDATETEXT permissions default to those users with SELECT permissions on the specified table. Permissions are transferable when SELECT permissions are transferred.

Examples

This example puts the text pointer into the local variable @ptrval, and then uses UPDATETEXT to update a spelling error.

USE pubs

GO

EXEC sp_dboption 'pubs', 'select into/bulkcopy', 'true'

GO

DECLARE @ptrval binary(16)

SELECT @ptrval = TEXTPTR(pr_info)

    FROM pub_info pr, publishers p

        WHERE p.pub_id = pr.pub_id

        AND p.pub_name = 'New Moon Books'

UPDATETEXT pub_info.pr_info @ptrval 88 1 'b'

GO

EXEC sp_dboption 'pubs', 'select into/bulkcopy', 'false'

GO

See Also