locked
text field RRS feed

  • 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)
    Sunday, December 23, 2012 12:44 PM

Answers

All replies

  • 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

    Sunday, December 23, 2012 1:05 PM
  • 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, esquel@sommarskog.se
    Sunday, December 23, 2012 3:07 PM
  • 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


    • Edited by Iric Wen Monday, December 24, 2012 9:37 AM
    • Marked as answer by Iric Wen Wednesday, December 26, 2012 3:04 AM
    Monday, December 24, 2012 7:53 AM
    • Proposed as answer by Naomi N Monday, December 24, 2012 5:31 PM
    • Marked as answer by Iric Wen Wednesday, December 26, 2012 3:04 AM
    Monday, December 24, 2012 8:40 AM
  • Egads, someone is rehashing a question from 2008?!?!?!?!?


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Monday, December 24, 2012 10:44 AM
  • Seems to be word by word the same question.

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


    My blog

    Monday, December 24, 2012 5:32 PM