SQL Server stores more than 8000 characters

Document directory

• Modify the updatetext field:
• Read field readtext:

SQL Server 2000 provides functions for processing text, ntext, and image fields. They are:

Textptr
Textvalid
Readtext
Updatetext
Writetext

General functions:

Writing segment writetext:

Declare @ Val varbinary (16)
Select @ val = textptr (field name) from table name where Condition
Writetext table name. Field name @ Val 'your long string, generally greater than 8000, not greater than the row'
-- Note: adding a condition in the second row can locate the record of the current operation.

Modify the updatetext field:

Declare @ Val varbinary (16)
Select @ val = textptr (field name) from table name where Condition
Updatetext table name. Field name @ Val 0 null 'your long string, generally greater than 8000, not greater than'
-- Note: adding a condition in the second row can locate the record of the current operation. 0 in the third row indicates that the field starts from 0th characters and null indicates that, all characters starting from 0 to the end of this field are deleted

Read field readtext:

Declare @ Val varbinary (16)
Select @ val = textptr (field name) from table name where Condition
Readtext table name. Field name @ Val 0 40000
-- Note: The second line can be used to locate the record of the current operation with the condition. The second line has two parameters, meaning: Read 0th characters starting from 40000 characters.
-- You can think that this function is useless at all. Yeah, I don't think it's useful to read it. You can read it directly.

Original Author: yongfa365 blog

 

 

Official notes:

 

Updatetext

Update existingText,NtextOrImage Field. Use updatetext to change at an appropriate locationText,NtextOrImage A part of a column. Use writetext to update and replace the entireText,NtextOrImage Field.

Syntax

UPDATETEXT { 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 } ]

Parameters

Table_name.Dest_column_name

The table to be updated andText,NtextOrImageColumn name. The table name and column name must comply with the identifier rules. For more information, see use identifiers. The specified database name and owner name are optional.

Dest_text_ptr

Point toText,NtextOrImageThe value of the Data Text pointer (returned by the textptr function ).Dest_text_ptrMust beBinary (16).

Insert_offset

Start position of a zero-based update. ForTextOrImageColumn,Insert_offsetIs the number of bytes to be skipped from the start point of the existing column before new data is inserted.NtextColumn,Insert_offsetIs the number of characters (eachNtext2 bytes ). Starting from the existing zero-based starting pointText,NtextOrImageShift Data to the right to free up space for new data. If the value is 0, the new data is inserted at the beginning of the existing location. If the value is null, the new data is appended to the existing data value.

Delete_length

YesfromInsert_offsetStarting from the currentText,NtextOrImageThe length of the deleted data in the column.Delete_lengthValueTextAndImageColumns are specified in bytes.NtextColumns are specified with characters. EachNtextThe character occupies 2 bytes. If the value is 0, data is not deleted. If the value is null, Delete the existingTextOrImageFromInsert_offsetAll data from the beginning to the end.

With log

Is ignored in Microsoft SQL Server 2000. In this version, log records are determined by the effective recovery model of the database.

Inserted_data

Yes to insert to existingText,NtextOrImageColumnInsert_offsetLocation data. This is a singleChar,Nchar,Varchar,Nvarchar,Binary,Varbinary,Text,NtextOrImageValue.Inserted_dataIt can be text or variable.

Table_name.src_column_name

Used to insert a table orText,NtextOrImageColumn name. The table name and column name must comply with the identifier rules.

Src_text_ptr

Point toText,NtextOrImageThe text pointer value of the column (returned by the textptr function ).

 

Writetext

Allow existingText,NtextOrImageColumns for interactive updates without logging. This statement will completely overwrite any existing data in the columns affected by it. The writetext statement cannot be used in the view.Text,NtextAndImageColumn.

Syntax

WRITETEXT { table.column text_ptr }
[ WITH LOG ] { data }

Parameters

Table.Column

The table to be updated andText,NtextOrImageColumn name. The table name and column name must comply with the identifier rules. For more information, see use identifiers. The specified database name and owner name are optional.

Text_ptr

PointText,NtextOrImageThe value of the Data Pointer.Text_ptrThe data type of must beBinary (16). To create a text pointerText,NtextOrImageColumns use non-null data to execute insert or update statements. For more information about creating text pointers, see insert or update.

With log

Ignore in Microsoft SQL Server 2000. Log records are determined by the actual recovery model of the database.

Data

ActualText,NtextOrImageData.DataIt can be a literal or a variable. ForText,NtextAndImageThe maximum length of the text that can be inserted using writetext is approximately 120 kb.