Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
Author |
Topic |
egtusr
Starting Member
2 Posts |
Posted - 2004-08-24 : 14:50:50
|
Hi,Is there any way to concatenate to a TEXT field? the max length of 8000 for varchar is not enough and so had to create a text field in the table and now I need to append more data to it. Any help would be greatly appreciated!Thanks |
|
derrickleggett
Pointy Haired Yak DBA
4184 Posts |
Posted - 2004-08-24 : 14:58:11
|
From Books Online:WRITETEXTPermits nonlogged, interactive updating of an existing text, ntext, or image column. This statement completely overwrites any existing data in the column it affects. WRITETEXT cannot be used on text, ntext, and image columns in views.SyntaxWRITETEXT { table.column text_ptr } [ WITH LOG ] { data }Argumentstable.columnIs the name of the table and text, ntext, or image column to update. Table and column names must conform to the rules for identifiers. For more information, see Using Identifiers. Specifying the database name and owner names is optional.text_ptrIs a value that stores the pointer to the text, ntext or image data. text_ptr must be binary(16). To create a text pointer, execute an INSERT or UPDATE statement with data that is not NULL for the text, ntext, or image column. For more information about creating a text pointer, see either INSERT or UPDATE. WITH LOGIgnored in Microsoft® SQL Server™ 2000. Logging is determined by the recovery model in effect for the database.dataIs the actual text, ntext or image data to store. data can be a literal or a variable. The maximum length of text that can be inserted interactively with WRITETEXT is approximately 120 KB for text, ntext, and image data. RemarksUse WRITETEXT to replace text, ntext, and image data and UPDATETEXT to modify text, ntext, and image data. UPDATETEXT is more flexible because it changes only a portion of a text, ntext, or image column rather than the entire column.If the database recovery model is simple or bulk-logged, WRITETEXT is a nonlogged operation. This means text, ntext, or image data is not logged when it is written to the database; therefore, the transaction log does not fill up with the large amounts of data that often make up these data types.For WRITETEXT to work properly, the column must already contain a valid text pointer. If the table does not have in row text, SQL Server saves space by not initializing text columns when explicit or implicit null values are placed in text columns with INSERT, and no text pointer can be obtained for such nulls. To initialize text columns to NULL, use the UPDATE statement. If the table has in row text, there is no need to initialize the text column for nulls and you can always get a text pointer.The DB-Library dbwritetext and dbmoretext functions and the ODBC SQLPutData function are faster and use less dynamic memory than WRITETEXT. These functions can insert up to 2 gigabytes of text, ntext, or image data.In SQL Server 2000, in row text pointers to text, ntext, or image data may exist but be invalid. For information about the text in row option, see sp_tableoption. For information about invalidating text pointers, see sp_invalidate_textptr.PermissionsWRITETEXT permissions default to those users with SELECT permissions on the specified table. Permissions are transferable when SELECT permissions are transferred.ExamplesThis example puts the text pointer into the local variable @ptrval, and then WRITETEXT places the new text string into the row pointed to by @ptrval.USE pubsGOEXEC sp_dboption 'pubs', 'select into/bulkcopy', 'true'GODECLARE @ptrval binary(16)SELECT @ptrval = TEXTPTR(pr_info) FROM pub_info pr, publishers pWHERE p.pub_id = pr.pub_id AND p.pub_name = 'New Moon Books'WRITETEXT pub_info.pr_info @ptrval 'New Moon Books (NMB) has just released another top ten publication. With the latest publication this makes NMB the hottest new publisher of the year!'GOEXEC sp_dboption 'pubs', 'select into/bulkcopy', 'false'GOSee AlsoData TypesDECLARE @local_variableDELETESELECTSETUPDATETEXT©1988-2000 Microsoft Corporation. All Rights Reserved. MeanOldDBAderrickleggett@hotmail.comWhen life gives you a lemon, fire the DBA. |
|
|
egtusr
Starting Member
2 Posts |
Posted - 2004-08-24 : 15:04:49
|
Hi,But how can I add more text to the exising text? I cannot use string concatenation operator (+). Lets assume the exising text is 'TEST' and I want to append 'More' to it. As per your reply, Writetext overwrites the entire field.Thanks again! |
|
|
derrickleggett
Pointy Haired Yak DBA
4184 Posts |
Posted - 2004-08-24 : 21:20:12
|
Whoops. Sorry about that. BOL to the rescue again though.UPDATETEXT Topic last updated -- July 2003Updates an existing text, ntext, or image field. Use UPDATETEXT to change only a portion of a text, ntext, or image column in place. Use WRITETEXT to update and replace an entire text, ntext, or image field.SyntaxUPDATETEXT { table_name.dest_column_name dest_text_ptr } { NULL | insert_offset } { NULL | delete_length } [ WITH LOG ] [ inserted_data | { table_name.src_column_name src_text_ptr } ]Argumentstable_name.dest_column_nameIs the name of the table and text, ntext, or image column to be updated. Table names and column names must conform to the rules for identifiers. For more information, see Using Identifiers. Specifying the database name and owner names is optional.dest_text_ptrIs a text pointer value (returned by the TEXTPTR function) that points to the text, ntext, or image data to be updated. dest_text_ptr must be binary(16).insert_offsetIs the zero-based starting position for the update. For text or image columns, insert_offset is the number of bytes to skip from the start of the existing column before inserting new data. For ntext columns, insert_offset is the number of characters (each ntext character uses 2 bytes). The existing text, ntext, or image data beginning at this zero-based starting position is shifted to the right to make room for the new data. A value of 0 inserts the new data at the beginning of the existing data. A value of NULL appends the new data to the existing data value.delete_lengthIs the length of data to delete from the existing text, ntext, or image column, starting at the insert_offset position. The delete_length value is specified in bytes for text and image columns and in characters for ntext columns. Each ntext character uses 2 bytes. A value of 0 deletes no data. A value of NULL deletes all data from the insert_offset position to the end of the existing text or image column.WITH LOGIgnored in Microsoft® SQL Server™ 2000. In this release, logging is determined by the recovery model in effect for the database.inserted_dataIs the data to be inserted into the existing text, ntext, or image column at the insert_offset location. This is a single char, nchar, varchar, nvarchar, binary, varbinary, text, ntext, or image value. inserted_data can be a literal or a variable.table_name.src_column_nameIs the name of the table and text, ntext, or image column used as the source of the inserted data. Table names and column names must conform to the rules for identifiers.src_text_ptrIs a text pointer value (returned by the TEXTPTR function) that points to a text, ntext, or image column used as the source of the inserted data.RemarksNewly inserted data can be a single inserted_data constant, table name, column name, or text pointer.Update action UPDATETEXT parameters To replace existing data Specify a nonnull insert_offset value, a nonzero delete_length value, and the new data to be inserted. To delete existing data Specify a nonnull insert_offset value and a nonzero delete_length. Do not specify new data to be inserted. To insert new data Specify the insert_offset value, a delete_length of 0, and the new data to be inserted. In SQL Server 2000, in row text pointers to text, ntext, or image data may exist but be invalid. For information about the text in row option, see sp_tableoption. For information about invalidating text pointers, see sp_invalidate_textptr.To initialize text columns to NULL, use UPDATETEXT when the compatibility level is equal to 65. If the compatibility level is equal to 70, use WRITETEXT to initialize text columns to NULL; otherwise, UPDATETEXT initializes text columns to an empty string. For information about setting the compatibility level, see sp_dbcmptlevel. PermissionsUPDATETEXT permissions default to those users with UPDATE permissions on the specified table. Permissions are transferable when UPDATE permissions are transferred.ExamplesThis example puts the text pointer into the local variable @ptrval, and then uses UPDATETEXT to update a spelling error.USE pubsGOEXEC sp_dboption 'pubs', 'select into/bulkcopy', 'true'GODECLARE @ptrval binary(16)SELECT @ptrval = TEXTPTR(pr_info) FROM pub_info pr, publishers p WHERE p.pub_id = pr.pub_id AND p.pub_name = 'New Moon Books'UPDATETEXT pub_info.pr_info @ptrval 88 1 'b' GOEXEC sp_dboption 'pubs', 'select into/bulkcopy', 'false'GOSee AlsoREADTEXTTEXTPTRWRITETEXT MeanOldDBAderrickleggett@hotmail.comWhen life gives you a lemon, fire the DBA. |
|
|
|
|
|
|
|