Export (0) Print
Expand All
ABS
ALL
AND
ANY
AVG
bit
COS
COT
DAY
EXP
GO
IN
LEN
LOG
MAX
MIN
NOT
OR
PI
SET
SIN
STR
SUM
TAN
USE
VAR
Expand Minimize
2 out of 4 rated this helpful - Rate this topic

READTEXT

SQL Server 2000

Reads text, ntext, or image values from a text, ntext, or image column, starting from a specified offset and reading the specified number of bytes.

Syntax

READTEXT { table.column text_ptr offset size } [ HOLDLOCK ]

Arguments

table.column

Is the name of a table and column from which to read. Table and column names must conform to the rules for identifiers. Specifying the table and column names is required; however, specifying the database name and owner names is optional.

text_ptr

Is a valid text pointer. text_ptr must be binary(16).

offset

Is the number of bytes (when using the text or image data types) or characters (when using the ntext data type) to skip before starting to read the text, image, or ntext data. When using ntext data type, offset is the number of characters to skip before starting to read the data. When using text or image data types, offset is the number of bytes to skip before starting to read the data.

size

Is the number of bytes (when using the text or image data types) or characters (when using the ntext data type) of data to read. If size is 0, 4 KB bytes of data are read.

HOLDLOCK

Causes the text value to be locked for reads until the end of the transaction. Other users can read the value, but they cannot modify it.

Remarks

Use the TEXTPTR function to obtain a valid text_ptr value. TEXTPTR returns a pointer to the text, ntext, or image column in the specified row or to the text, ntext, or image column in the last row returned by the query if more than one row is returned. Because TEXTPTR returns a 16-byte binary string, it is best to declare a local variable to hold the text pointer and then use the variable with READTEXT. For more information about declaring a local variable, see DECLARE @local_variable.

In SQL Server 2000, in row text pointers may exist but be invalid. For more information about the text in row option, see sp_tableoption. For more information about invalidating text pointers, see sp_invalidate_textptr.

The value of the @@TEXTSIZE function supersedes the size specified for READTEXT if it is less than the specified size for READTEXT. The @@TEXTSIZE function is the limit on the number of bytes of data to be returned set by the SET TEXTSIZE statement. For more information about how to set the session setting for TEXTSIZE, see SET TEXTSIZE.

Permissions

READTEXT permissions default to users with SELECT permissions on the specified table. Permissions are transferrable when SELECT permissions are transferred.

Examples

This example reads the second through twenty-sixth characters of the pr_info column in the pub_info table.

USE pubs
GO
DECLARE @ptrval varbinary(16)
SELECT @ptrval = TEXTPTR(pr_info) 
   FROM pub_info pr INNER JOIN publishers p
      ON pr.pub_id = p.pub_id 
      AND p.pub_name = 'New Moon Books'
READTEXT pub_info.pr_info @ptrval 1 25
GO

See Also

@@TEXTSIZE

UPDATETEXT

WRITETEXT

Did you find this helpful?
(1500 characters remaining)
Thank you for your feedback
Show:
© 2014 Microsoft. All rights reserved.