Using text, ntext, and image Functions

The following functions are used exclusively for operations on text, ntext, and image data:

• TEXTPTR returns a binary(16) object that contains 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 that are used to change text, ntext, and image data.

In Transact-SQL statements, text, ntext, and image data are always referenced by using pointers or the address of the data.

The following example uses the TEXTPTR function to locate the text column (pr_info) associated with pub_id0736 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. This 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 10

Here is the result set.

(1 row(s) affected)

pr_info
----------------------------------------
 is sample

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 performed. For example: CAST( CAST( text_column_name AS varchar(10) ) AS int ).