SQLSERVER Database Long Text and Binary Field Operation Summary (54POWERMAN Original) - Create Test Table
Create Table TestnText (Rowid Int Idness "Primary Key, Content NText)
- Read and write NTEXT text, be sure to use in things
Begin TRAN
Declare @Ptrval Varbinary (16)
Select @ Ptrval = TextPtr (Content) from testntext where rowid = 1
--TextPtr function Returns the text pointer value corresponding to Text, Ntext, or Image column in Varbinary Format. The retrieved text pointer value can be used for ReadText, WriteText, and UpdateText statements.
SELECT ROWID, DATALENGTH (Content) from testnText
- To get the length of Content content, use the DATALEENGTH function to get, the result is byte
- To use line text pointers, get pointers first
Readtext TestnText.content @Ptrval 1 10
- The first parameter is a field, must use the table name limit field,
- The third parameter is the number of bytes skip before starting reading text, image, or ntext data. If you choose to start from start
- The fourth parameter is the length of the selected. Is the number of bytes to read data (when using the text or image data type) or the number of characters (when using the NTEXT data type).
- The fifth parameter is optional. If the value is HOLDLOCK, it indicates that the text value has been locked to the end of the transaction. Other users can read this value but cannot be modified.
Set Textsize 5
- If you specify TEXTSIZE at this time, you will limit the read read.
Readtext TestnText.content @Ptrval 1 10
- Since the TEXTSIZE is specified above, it returns less than 10 bytes of data, but only 5 bytes.
WriteText TestnText.content @ptrval 'Please replace Text, NText, and ImageText, and use UpdateText to modify Text, NText, and Image data. UpdateText is more flexible because it only changes a part of Text, NText, or Image column, not the entire column. If the database recovery model is simple or a large-capacity log record, WRITETEXT is an operation without a logged record. This means that logging is not logged when writing Text, NText or Image data, and therefore, the transaction log does not fill data that is usually constructed by these data types. In order to make WRITETEXT work normally, the column must already contain a valid text pointer. If the table does not have an inline text, the SQL Server does not initialize the Text column when it is placed in the Text column through INSERT, saving space and cannot obtain this type of null value text pointer. To initialize the Text column to null, use the UPDATE statement. If the table has in-line text, there is no need to initialize the text column for null value, and you can always get the text pointer. DB-Library DBWRITEXT and DBMORETEXT functions compared to WriteText, and the ODBC SQLPUTDATA function is relatively fast and used. These functions can insert up to 2G bytes of Text, NTEXT, or Image data. '
- The third parameter is the text to be written, set textsize 0
- Release the TEXTSIZE settings set in front
Readtext TestnText.content @ptrval 0 100
UpdateText TestnText.content @ptrval 3 1 'Don't make'
- The third parameter is the starting position of the update of zero, or is the position of the inserted data.
- The fourth parameter is starting from the INSERT_OFFSET location, the length of the data to be deleted from the existing Text, NTEXT, or the Image column, or the data length to replace with the new data.
Readtext TestnText.content @ptrval 0 100
SELECT PATINDEX ('% Using%', Content) from testntext
Commit
/ *
Commonly used functions:
Patindex ('% PATTERN%', Expression) Returns a given string in the character position in the Text or NText column.
DATALENGTH (Expression) Returns the data length of the Text, NText, and Image columns.
Set TextSize Returns the limit size (in bytes) of the text, NTEXT, or Image data returned by the SELECT statement.
Substring (Text_Column, Start, Length) Returns the VARCHAR string specified by the given START offset and the length of the LENGTH. This length should be less than 8 kb.
TEXTVALID ('Table.Column', Text_PTR) A text, ntext, or image function, is used to check if the given text pointer is valid.
* /
