Text and Image Functions

Text and image functions return values commonly needed for operations on text and image data only. Text and image built-in function names are not keywords.

Syntax

function_name(parameters)

where

function_name
Specifies a text or image function.

These are the text and image functions:
Function Parameters Result
TEXTPTR (column_name) The text-pointer value in varbinary format. The text pointer is checked to ensure that it points to the first text page.
TEXTVALID ('table_name.column_name', text_ ptr) Checks whether a given text pointer is valid. Returns 1 if the pointer is valid and 0 if the pointer is invalid. Note that the identifier for the text column must include the table name.

Remarks

If a text or image column has not been initialized by an INSERT or UPDATE statement, TEXTPTR returns a null pointer. Use TEXTVALID to check whether a text pointer exists. You cannot use UPDATETEXT, WRITETEXT, or READTEXT without a valid text pointer.

In addition to these specific text and image functions, the following functions and statement are also useful with text and image data:

Function or statement Description
PATINDEX('%pattern%',
expression)		 The character position of a given character string in text columns. For details, see "String Functions" earlier in this topic.
DATALENGTH(expression) The length of data in text and image columns. For details, see "System Functions," earlier in this topic.
SET TEXTSIZE The limit, in bytes, of the text or image data to be returned with a SELECT statement. For details, see the SET statement.

Examples

A.    TEXTPTR Function

This example uses the TEXTPTR function to locate the image column logo associated with New Moon Books in the pub_info table of the pubs database. The text pointer is put into a local variable @ptrval.

DECLARE @ptrval varbinary(16)
SELECT @ptrval = TEXTPTR(logo) 
    FROM pub_info pr, publishers p
        WHERE p.pub_id = pr.pub_id 
        AND p.pub_name = 'New Moon Books'
B.    Return text Data

This example selects the pub_id column and the 16-byte text pointer of the pr_info column from the pub_info table.

SELECT pub_id, TEXTPTR(pr_info) FROM pub_info

To return the first 255 bytes of text:

SELECT pub_id, pr_info FROM pub_info
C.    TEXTVALID Function

This example reports whether a valid text pointer exists for each value in the logo column of the pub_info table.

SELECT pub_id, 'Valid (if 1) Text data' 
                = TEXTVALID ('pub_info.logo', TEXTPTR(logo)) 
    FROM pub_info