Category name´╝ÜUpdateText

Appending data to a sql text or ntext column

Today I came accross a problem where nvarchar data got larger than the maximum that can be stored in a record. I had to convert to a ntext column and append the data to it.

I quickly found out that I needed to use the UPDATETEXT function in combination with the TEXTPTR and DATALENGTH functions.

When you are working with the ntext type then the output of DATALENGTH must be divided by two to get the string length as it returns the length of the data in bytes.

DECLARE @TextToAppend nvarchar(max)
SET @TextToAppend = 'Append this piece of text'

DECLARE @pointer binary(16)
DECLARE @length int

SELECT
	@pointer = TEXTPTR(MyLargeTextColumn)
	,@length = DATALENGTH(MyLargeTextColumn)/2 -- Needed for ntext
FROM
	MyTableWithLargeTextColumn
WHERE
	ID = @ID

UPDATETEXT MyTableWithLargeTextColumn.MyLargeTextColumn @pointer @length 0 @TextToAppend

 

The above SQL code appends the text @TextToAppend to column MyTableWithLargeTextColumn.MyLargeTextColumn for the record with @ID.

  • Recent Posts
  • Recent Comments
  • Archives
  • Categories
  • Meta