Chapter 2: Transact-SQL Functions

textptr

Description

Returns a pointer to the first page of a text, image, or unitext column.

Syntax

textptr(column_name)

Parameters

column_name: is the name of a text column.

Examples

Example 1

Uses the textptr function to locate the text column, copy, associated with au_id 486-29-1786 in the author’s blurbs table. The text pointer is placed in local variable @val and supplied as a parameter to the readtext command, which returns 5 bytes, starting at the second byte (offset of 1):

declare @val binary(16) 
    select @val = textptr(copy) from blurbs
    where au_id = "486-29-1786"
    readtext blurbs.copy @val 1 5

Example 2

Selects the title_id column and the 16-byte text pointer of the copy column from the blurbs table:

select au_id, textptr(copy) from blurbs

Usage

textptr, a text and image function, returns the text pointer value, a 16-byte varbinary value.
If a text, unitext, or image column has not been initialized by a non-null insert or by any update statement, textptr returns a NULL pointer. Use textvalid to check whether a text pointer exists. You cannot use writetext or readtext without a valid text pointer.
For general information about text and image functions, see “Text and image functions”.

Trailing f in varbinary values are truncated when the values are stored in tables. If you are storing text pointer values in a table, use binary as the datatype for the column.

Standards

ANSI SQL – Compliance level: Transact-SQL extension.

Permissions

Any user can execute textptr.