text field

Question

So I need to update a text field. Neither the UPDATE statement or the WRITETEXT statement work when used below

CREATE TABLE MyTable (IDField int, MyField text)
INSERT INTO MyTable (IDField) SELECT 1

DECLARE @Data1 varchar(8000), @Data2 varchar(8000), @ptrval binary(16)

SELECT @Data1 = REPLICATE('1',8000)
SELECT @Data2 = REPLICATE('2',8000)

-- this sets MyField to string of only 8000 characters
UPDATE MyTable SET MyField = @Data1 + @Data2 WHERE IDField = 1 


SELECT @ptrval = TEXTPTR(MyField ) 
FROM MyTable 
WHERE IDField = 1 

-- this causes an error: Incorrect syntax near '+'.
--WRITETEXT MyTable.MyField @ptrval @Data1 + @Data2
How am I supposed to do this when local variables cannot be of type TEXT? (If I had SSQL Server 2005 I would use varchar(max) - but I don't)

Answer 1

It worked for me using SQL Server 2012.

CREATE TABLE MyTable (IDField int, MyField text)
 INSERT INTO MyTable (IDField) SELECT 1
 
DECLARE @Data1 varchar(8000), @Data2 varchar(8000), @ptrval binary(16)
 
SELECT @Data1 = REPLICATE('1',8000)
 SELECT @Data2 = REPLICATE('2',8000)
 
-- this sets MyField to string of only 8000 characters
 UPDATE MyTable SET MyField = @Data1 + @Data2 WHERE IDField = 1 
 

SELECT TEXTPTR(MyField ), MyField
 FROM MyTable 
 WHERE IDField = 1 
 GO
 -- 0xFFFF45C6900100002501000001000000	1111111111111111
 DROP TABLE MyTable;

 

---

Kalman Toth SQL 2008 GRAND SLAM
New Book: SQL Programming & Database Design Using Microsoft SQL Server 2012

Answer 2

Could you give as the full story of what you are trying to do? Working with text/ntext/image is very cumbersome, so knowing the full context is valuable as it can help to find shortcuts.

There is UPDATETEXT, but it's not the simplest command to use.

---

Erland Sommarskog, SQL Server MVP, [e-mail]

Answer 3

Hi Ezreal,

Try using UPDATETEXT instead
WRITETEXT MyTable.MyField @ptrval @Data1
UPDATETEXT MyTable.MyField @ptrval 8000 NULL @Data2
The insert offset is zero based so 8000 should write into the 8001st character. The delete offset is null as a value of NULL deletes all data from the insert_offset position to the end of the existing text.

http://msdn.microsoft.com/en-us/library/ms189466.aspx

Do not forget nvarchar (which you should use with ntext field) have a maximum capacity of half the varchar fields that you are using so your block sizes need to be reduced to 4000 in that case.

Reference:

http://stackoverflow.com/questions/204170/how-to-update-a-text-or-ntext-field-in-sql-server-2000

---

Iric Wen
TechNet Community Support

Answer 4

Déjà vu?

http://stackoverflow.com/questions/204170/how-to-update-a-text-or-ntext-field-in-sql-server-2000

---

Serg

Answer 5

Egads, someone is rehashing a question from 2008?!?!?!?!?

---

Erland Sommarskog, SQL Server MVP, [e-mail]

Answer 6

Seems to be word by word the same question.

---

For every expert, there is an equal and opposite expert. - Becker's Law


My blog