Was this page helpful?
Your feedback about this content is important. Let us know what you think.
Additional feedback?
1500 characters remaining
Export (0) Print
Expand All

Modifying ntext, text, or image Values

SQL Server 2000

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

Show:
© 2015 Microsoft