Using text, ntext, and image Functions
There are two text, ntext, and image functions used exclusively for operations on text, ntext, and image data:
- TEXTPTR returns a binary(16) object containing a pointer to a text, ntext, or image instance. The pointer remains valid until the row is deleted.
- TEXTVALID function checks whether a specified text pointer is valid or not.
Text pointers are passed to the READTEXT, UPDATETEXT, WRITETEXT, PATINDEX, DATALENGTH, and SET TEXTSIZE Transact-SQL statements used to manipulate text, ntext, and image data.
In Transact-SQL statements, text, ntext, and image data is always referenced using pointers or the address of the data.
This example uses the TEXTPTR function to locate the text column (pr_info) associated with pub_id 0736 in the pub_info table of the pubs database. It first declares the local variable @val. The text pointer (a long binary string) is then put into @val and supplied as a parameter to the READTEXT statement, which returns 10 bytes starting at the fifth byte (offset of 4).
USE pubs
DECLARE @val varbinary(16)
SELECT @val = textptr(pr_info) FROM pub_info
WHERE pub_id = '0736'
READTEXT pub_info.pr_info @val 4 4
Here is the result set:
(1 row(s) affected)
pr_info
----------------------------------------
yet
Explicit conversion using the CAST function is supported from text to varchar, from ntext to nvarchar, and from image to varbinary or binary, but the text or image data is truncated to 8,000 bytes and ntext data is truncated at 4,000 characters (8,000 bytes). Conversion of text, ntext, or image to another data type is not supported, implicitly or explicitly. However, indirect conversion of text, ntext or image data can be done, for example:
CAST( CAST( text_column_name AS VARCHAR(10) ) AS INT ).