Modifying ntext, text, or image Values

You can modify ntext, text, or image values by:

• Using a database API such as ADO, OLE DB, or ODBC to execute an UPDATE or INSERT statement with a program variable bound to a parameter marker for the ntext, text, or image column. Then call the appropriate database API functions to send long data to the database one block at a time. DB-Library supports the same functionality with its text and image functions.
  
  
• Using the WRITETEXT statement to rewrite the entire data value for the column.

  For example, this query changes the contents of the pr_info column for New Moon Books:

  USE pubs
  sp_dboption 'pubs', 'select into/bulkcopy', 'true'
  DECLARE @ptrval varbinary(16)
  SELECT @ptrval = TEXTPTR(pr_info) 
  FROM pub_info pr INNER JOIN publishers p
      ON p.pub_id = pr.pub_id 
      AND p.pub_name = 'New Moon Books'
  WRITETEXT pub_info.pr_info @ptrval 'New Moon Books (NMB) '
  sp_dboption 'pubs', 'select into/bulkcopy', 'true'
  

• Use the UPDATETEXT statement to update specific blocks of an ntext, text, or image column.

  For example, this query replaces the eighty-eighth character in the text column for New Moon Books (the second letter o in Moon) with the letter z:

  USE pubs
  sp_dboption 'pubs', 'select into/bulkcopy', 'true'
  DECLARE @ptrval varbinary(16)
  SELECT @ptrval = TEXTPTR(pr_info) 
      FROM pub_info pr INNER JOIN publishers p
          ON p.pub_id = pr.pub_id 
          AND p.pub_name = 'New Moon Books'
  UPDATETEXT pub_info.pr_info @ptrval 88 1 'z' 
  sp_dboption 'pubs', 'select into/bulkcopy', 'false'
  

See Also

WRITETEXT

UPDATETEXT

Managing Long Data Types

BLOBs and OLE Objects

Managing text and image Columns

Text and Image Functions