Appending data to a sql text or ntext column

By ramon
June 20, 2008

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

	@pointer = TEXTPTR(MyLargeTextColumn)
	,@length = DATALENGTH(MyLargeTextColumn)/2 -- Needed for ntext
	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.

Comments: 1

  1. Frank says:

    Thank you so much for posting this – it was very straight forward and did exactly what I was looking for.

Leave a Reply

Your email address will not be published. Required fields are marked *

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