READTEXT (Transact-SQL)

Applies to: SQL Server Azure SQL Database Azure SQL Managed Instance

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

Important

This feature will be removed in a future version of SQL Server. Avoid using this feature in new development work, and plan to modify applications that currently use this feature. Use the SUBSTRING function instead.

Transact-SQL syntax conventions

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 fulfill 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 the text or image data types are used. It can also be the number of bytes for characters when the ntext data type is used to skip before it starts to read the text, image, or ntext data.

size Is the number of bytes when the text or image data types are used. It can also be the number of bytes for characters when the ntext data type is used for data to read. If size is 0, 4 KB of data is 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 can't 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. TEXTPRT can also return a pointer or to the text, ntext, or image column in the last row that the query returns if the query returns more than one row. Because TEXTPTR returns a 16-byte binary string, we recommend declaring 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 (Transact-SQL).

In SQL Server, in-row text pointers may exist but may not be valid. For more information about the text in row option, see sp_tableoption (Transact-SQL). For more information about invalidating text pointers, see sp_invalidate_textptr (Transact-SQL).

The value of the @@TEXTSIZE function supersedes the size specified for READTEXT if it's less than the specified size for READTEXT. The @@TEXTSIZE function specifies the limit on the number of data bytes returned which is set by the SET TEXTSIZE statement. For more information about how to set the session setting for TEXTSIZE, see SET TEXTSIZE (Transact-SQL).

Permissions

READTEXT permissions default to users that have SELECT permissions on the specified table. Permissions are transferable when SELECT permissions are transferred.

Examples

The following example reads the second through 26th characters of the pr_info column in the pub_info table.

Note

To run this example, you must install the pubs sample database.

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 (Transact-SQL)
UPDATETEXT (Transact-SQL)
WRITETEXT (Transact-SQL)