SQL Server NTEXT DataType
Update or Replace NTEXT fields in SQL Server
This article shows some methods of working with the ntext datatype in SQL Server 2000, Server 2008R2 for updating or replacing text.
This article will show the differences in working with VARCHAR vs NTEXT and the technical issues with the NTEXT datatype. First, we will create a table with both datatypes.
CREATE TABLE test
(ID int identity(1,1) not null primary key clustered,
txt ntext null,
txt2 varchar(200) null
)
Now, an insert of some test data for each field.
insert into test values ('testing 1,2,3', 'testing another varchar')
Update SQL Ntext field
Test to see if we can concatenate some data to the varchar field. It appears to work as expected.
--varchar is fine
update test
set
txt2 = txt2 + ' , 4,5,6'
WHERE ID =1
Concatentating an NTEXT field does not work as one would have expected it to work.
--ntext not so fine
update test
set
txt = txt + ' , 4,5,6'
WHERE ID =1
The error message displayed shows that the update failed.
Msg 402, Level 16, State 1, Line 1
The data types ntext and varchar are incompatible in the add operator.
Next, we try a Convert method on the data to enter to ntext and find that it doesn't work either.
update test
set
txt = txt + CONVERT(ntext, ' , 4,5,6')
WHERE ID =1
Another Error
Msg 8117, Level 16, State 1, Line 2
Operand data type ntext is invalid for add operator.
A convert to varchar(max) will work in SQL Server 2008 and above versions and the field will be concatenated.
UPDATE test
Set
txt = CONVERT(varchar(Max),txt) + ', 4,5,6'
WHERE ID =1
If you are working in SQL Server 2000, the VARCHAR(MAX) datatype is not available to you. There is an option to user of using UPDATETEXT with a TEXTPTR.
Note the conversion to varchar(max) to get the @offset. If left as LEN(txt), an error message would appear
Msg 8116, Level 16, State 1, Line 3
Argument data type ntext is invalid for argument 1 of len function.
Insert to a SQL Ntext Field
I will insert a new record to the table and attempt to append it with UPDATETEXT.
INSERT INTO test (txt) VALUES ('another test')
--update Text way
EXEC sp_dboption 'Development', 'select into/bulkcopy', 'true'
GO
DECLARE @ptrval binary(16)
DECLARE @offset int
SELECT @ptrval = TEXTPTR(txt), @offset = Len(convert(varchar(max),txt))
FROM test WHERE ID = 2
UPDATETEXT test.txt @ptrval @offset 0 ' to see if it works...'
GO
EXEC sp_dboption 'Development', 'select into/bulkcopy', 'false'
GO
Running a SELECT * FROM test shows that we are successful.
There is another function called WRITETEXT which will update\replace the ntext data using a similar type of structure with a TEXTPTR.
EXEC sp_dboption 'Development', 'select into/bulkcopy', 'true'
GO
DECLARE @ptrval binary(16)
DECLARE @offset int
SELECT @ptrval = TEXTPTR(txt), @offset = LEN(Convert(varchar(max),txt))
FROM test WHERE ID = 4
WRITETEXT test.txt @ptrval ' stuff to append to the ntext datatype'
GO
EXEC sp_dboption 'Development', 'select into/bulkcopy', 'false'
GO
The regular method of replacing text in an UPDATE statement in SSMS worked for me as well. The advantage to WRITETEXT is that the transaction is not logged and does not fill up the transaction log with large amounts of data.
--replacing text
update test
set
txt = CONVERT(ntext, 'another test with a convert')
WHERE ID = 1
update test
set
txt = 'another test without converting'
WHERE ID = 4
Records 1 & 4 are updated in the table.
