UPDATETEXT

UPDATETEXT is an SQL command used to make modifications to existing TEXT, NTEXT, or IMAGE data in a database. It is particularly relevant when dealing with large amounts of binary or character data, as it allows for only a certain portion of this data to be altered, instead of needing to modify the entire string or blob of data. Unlike the standard UPDATE statement, UPDATETEXT enables partial updates to large data types and helps in managing database performance.

UPDATETEXT( text_ptr, delete_offset, delete_length, insert_string )

  • text_ptr: This is a pointer to the text, ntext, or image column to be modified. It is usually retrieved using the TEXTPTR() function.
  • delete_offset: This integer value determines the starting point in the text column from where deletion of characters will begin.
  • delete_length: This integer parameter specifies the length or number of characters to delete from the text column starting from the delete_offset.
  • insert_string: This string parameter contains the text to insert at the deletion point after the specified number of characters have been deleted.

Example

DECLARE @ptrval binary(16);
SELECT @ptrval = TEXTPTR(Notes)
FROM employees
WHERE emp_id = 1;
UPDATETEXT employees.Notes @ptrval NULL 0 'New Text.';

Output

(1 row affected)

Explanation

In the provided SQL code, we first declare a binary variable called @ptrval. Then we select the text pointer of the Notes column in the employees table for the employee with emp_id equals to 1. The resulting text pointer is stored into @ptrval. After that, UPDATETEXT function is used to update the Notes field of our selected employee. This function starts replacing text in Notes from the very beginning (0th position) with ‘New Text.’. The Output section shows that one row was affected. The UPDATETEXT operation does not have specific output other than showing how many rows were affected by the operation.

For in-depth explanations and examples SQL keywords where you write your SQL, install our extension.