UPDATETEXT

SQL Server

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.