Text and Image Functions

Text functions are used for operations on text and image data. For a complete list of text and image functions, see Functions in the Microsoft SQL Server Transact-SQL Reference.

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):

DECLARE @val varbinary(16)
SELECT @val = textptr(pr_info) FROM pub_info
WHERE pub_id = '0736'
READTEXT pub_info.pr_info @val 4 10

(1 row(s) affected)

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

The TEXTPTR function returns a 16-character binary string. It is a good idea to put this string into a local variable, as in the preceding example, and use it by reference.

Explicit conversion using the CONVERT function is supported from text to varchar and from image to varbinary or binary, but the text or image data is truncated to 255 bytes. Conversion of text or image to another datatype is not supported, implicitly or explicitly.