SQL Server text and image functions

Text and image functions

　　1. Find a specific string patindex

Syntax is the same as String patindex.

　　2. Get text pointer textptr

When SQL Server stores text types (ntext, text), and image data types (images), the default is stored separately from data such as a common base data type (such as char, int, and so on), that is, if a field in a table has a base type and an image text type, Then, when the data is entered, the basic type and image file data are not stored on the same page, but are separated from the storage. After the storage is detached, the page in which the data for the base type data and the text image type is connected by a text pointer is a 16 byte binary type pointer. These pointers to data are stored in the basic type data page so that the text and image data are manipulated by pointers when needed. The TEXTPTR function can be used to obtain text pointers to text and image data stores, which can be manipulated by functions that manipulate text and image data (Wrutetext, READTEXT, UPDATETEXT, and so on). If there are no valid pointers, the text and image functions cannot manipulate the data.

Syntax structure:

TEXTPTR (column)

The column here is the name of the text and image data columns (text, ntext, and image) in the table.

return value:

varbinary type 16-byte pointer.

Functions used in conjunction with:

The functions shown in the following table are often used when writing T-SQL code that contains TEXTPTR functions.

Format	Description
Patindex ('%pattern% ', expression)	Returns the character position of a string in the text or ntext column
DATALENGTH (expression)	Returns the length of data in the text, ntext, and image columns
SET TEXTSIZE	Returns the limit (in bytes) of the text, ntext, or image data returned by the SELECT statement. Settextsize 0 means no limit.
SUBSTRING (text_column,start,length)	Returns a string that specifies the start offset and the varchar type specified by length in a binary column. The length of the string is hard less than 8kb

　　3. Check if text pointer is valid TEXTVALID

The Textvalid function is used to detect whether a text pointer is valid.

Syntax structure:

Textvalid (' table.column ', text_ptr)

Parameter description:

Table: Names of tables that must contain

Column: The names of binary data columns (text, ntext, and image) in the table

Text_ptr: The text pointer to be detected

return value:

int, if the pointer returns 1 effectively, otherwise the point will be 0.

　　4, read the text image column content ReadText

The READTEXT function is used to read the contents of a text or image column, starting at the specified offset to read the specified number of bytes. If it is an image column, this reads the number of bytes, not the actual image itself.

Syntax structure:

readtext{table.column text_ptr offset size} [Holdlock]

Parameter description:

Table: Tables Name

Column Name:

TEXT_PTR: A valid text pointer, which is 16 bytes of binary data.

Offset: The number of bytes to skip before starting to read the text, image type data. The number of characters to skip before reading the ntext type data.

Size: The number of bytes read (when using the text or image data type) or the number of characters (when using the ntext data type). If size is 0, the 4KB data is read.

Holdlock: Causes the text value to be locked for reading until the end of XV. The value can be read by other users, but it cannot be modified.

return value:

varchar character.

Example: Write later

　　5. Write text image column content WriteText

The WRITETEXT function is used to write content to text or images, and can optionally be minimally logged, so that write operations produce less log writes than I/O, and have little impact on database performance.

Syntax structure:

writetext{table.column text_ptr} [with log] {data}

Parameter description:

Table: Tables Name

Column Name:

TEXT_PTR: A valid text pointer, which is 16 bytes of binary data.

With log: The database's recovery model determines the log behavior generated by the WRITETEXT operation. Because the text, ntext, IMGE type of data is generally relatively large. For example, a picture may be hundreds of KB, we know that a data page capacity is the standard 8KB. So a picture is likely to need a few more data pages, so the WRITETEXT operation produces many I/O times, and excessive I/O times will affect the performance of the database. The introduction of this parameter is given to the database's recovery model to determine the log behavior that will result from the WRITETEXT operation. When a database doll is in full recovery mode, the maximum number of logs is generated, so the impact on database performance is greatest. When working in the simple recovery model, the resulting log is minimal.

Data: The text, ntext, or image type that you want to store. WRITETEXT the maximum text length that is inserted interactively is approximately 120KB.

　　6. Update text image column content UpdateText

The UPDATETEXT function updates the contents of a text or image column. The difference from WRITETEXT is that the function can update only a portion of a column, while WRITETEXT updates the entire column.

Syntax structure:

updatetext{Table.name.dest_column_name dest_text_ptr} {null | insert_offset} {with log} {Inserted_data | {Table.name.src_column_name Src_text_ptr}}

The parameters in the syntax are described below.

TABLE_NAME: Table name.

Dest_column_name: Column name.

DEST_TEXT_PTR: A valid text pointer, which is 16 bytes of binary data.

Insert_offset: 0-based update location, for text or image columns, Insert_offset is the number of bytes to skip from the starting point of an existing column before inserting new data. For ntext columns, Insert_offset is the number of characters (each ntext character occupies 2 bytes). The existing text, ntext, or image data starting at the starting point of the base zero shifts to the right, leaving room for the new data. A value of 0 means that new data is inserted at the beginning of the existing data. A value of NULL appends the new data to the Access data value.

Delete_length: The length of data to remove from the existing text, ntext, or image columns, starting from the insert_offset position. The delete_length value is specified in bytes for the text and image columns, and for ntext columns with characters. Each ntext character occupies 2 bytes. A value of 0 means that the data is not deleted. A value of NULL deletes any data from the existing text or image column starting at the insert_offset position to the end.

Withlog: The day record is determined by the current schema of the database.

Inserted_date: Data to insert the insert_offset position of an existing text, ntext, or image column. 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_name: The name of the table or text, ntext, image column that is used to insert the data source.

SRC_TEXT_PTR: Pointer to the text pointer value used as the literal, ntext, or image column of the inserted data source, usually obtained using the TEXTPTR function.

SQL Server text and image functions