UPDATETEXT (Transact-SQL)

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

Important

This feature will be removed in a future version of Microsoft SQL Server. Avoid using this feature in new development work, and plan to modify applications that currently use this feature. Use the large-value data types and the .WRITE clause of the UPDATE statement instead.

Transact-SQL Syntax Conventions

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 comply with the rules for 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_offsetis the number of characters (each ntext character uses 2 bytes). The existing text, ntext, or image data starting 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_lengthvalue 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
  Ignored in SQL Server 2000. In SQL Server 2005, logging is determined by the recovery model in effect for the database.

• 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 comply with 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.

  Note

  scr_text_ptrvalue must not be the same as dest_text_ptrvalue.

Remarks

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

Update action	UPDATETEXT parameters
To replace existing data	Specify a nonnull insert_offset value, a nonzero delete_length value, and the new data to be inserted.
To delete existing data	Specify a nonnull insert_offset value and a nonzero delete_length. Do not specify new data to be inserted.
To insert new data	Specify the insert_offset value, a delete_length of 0, and the new data to be inserted.

For best performance we recommend that text, ntext and image data be inserted or updated in chunks sizes that are multiples of 8,040 bytes.

In SQL Server, in-row text pointers to text, ntext, or image data may exist but may not be valid. For information about the text in row option, see sp_tableoption (Transact-SQL). For information about invalidating text pointers, see sp_invalidate_textptr (Transact-SQL).

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 (Transact-SQL).

Permissions

Requires UPDATE permission on the specified table.

Examples

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

Note

To run this example, you must install the pubs database. For information about how to install the pubs database, see Downloading Northwind and pubs Sample Databases.

USE pubs;
GO
ALTER DATABASE pubs SET RECOVERY SIMPLE;
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
ALTER DATABASE pubs SET RECOVERY FULL;
GO

See Also

Reference

READTEXT (Transact-SQL)
TEXTPTR (Transact-SQL)
WRITETEXT (Transact-SQL)

Other Resources

Using Large-Value Data Types

Help and Information

Getting SQL Server 2005 Assistance