Appending data to a sql text or ntext column

Published 20 June 8 1:52 PM | Ramon Smits

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.

Filed under: ,

Comments

# Thommy Mewes said on October 7, 2008 3:11 PM:

Vielleicht hat der eine oder andere schon einmal probiert, auf eine Spalte vom Typ ntext ein Update zu fahren, was Daten an die Spalte anhängt und ist dabei genauso kläglich gescheitert wie ich. Die Lösung bietet ein Blog-Eintrag von Ramon