Reads text and image values, starting from a specified offset and reading a specified number of bytes or characters.
readtext [[database.]owner.]table_name.column_name
text_pointer offset size
[holdlock | noholdlock] [readpast]
[using {bytes | chars | characters}]
[at isolation {
[ read uncommitted | 0 ] |
[ read committed | 1 ] |
[ repeatable read | 2 ]|
[ serializable | 3 ] } ]
is the name of the text or image column. You must include the table name. Specify the database name if the table is in another database, and specify the owner’s name if more than one table of that name exists in the database. The default value for owner is the current user, and the default value for database is the current database.
is a varbinary(16) value that stores the pointer to the text or image data. Use the textptr function to determine this value (see Example 1). text and image data is not stored in the same set of linked pages as other table columns. It is stored in a separate set of linked pages. A pointer to the actual location is stored with the data; textptr returns this pointer.
specifies the number of bytes or characters to skip before starting to read text or image data.
specifies the number of bytes or characters of data to read.
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.
prevents the server from holding any locks acquired during the execution of this statement, regardless of the transaction isolation level currently in effect. You cannot specify both a holdlock and a noholdlock option in a query.
specifies that readtext should silently skip rows with exclusive locks, without waiting and without generating a message.
specifies whether readtext interprets the offset and size parameters as a number of bytes (bytes) or as a number of textptr characters (chars or characters are synonymous). This option has no effect when used with a single-byte character set or with image values (readtext reads image values byte by byte). If the using option is not given, readtext interprets the size and offset arguments as bytes.
specifies the isolation level (0, 1, or 3) of the query. If you omit this clause, the query uses the isolation level of the session in which it executes (isolation level 1 by default). If you specify holdlock in a query that also specifies at isolation read uncommitted, Adaptive Server issues a warning and ignores the at isolation clause. For the other isolation levels, holdlock takes precedence over the at isolation clause.
specifies isolation level 0 for the query. You can specify 0 instead of read uncommitted with the at isolation clause.
specifies isolation level 1 for the query. You can specify “1” instead of read committed with the at isolation clause.
specifies isolation level 2 for the query. You can specify “2” instead of serializable with the at isolation clause.
specifies isolation level 3 for the query. You can specify “3” instead of serializable with the at isolation clause.
Selects the second through the sixth character of the copy column:
declare @val varbinary(16) select @val = textptr(copy) from blurbs where au_id = "648-92-1872" readtext blurbs.copy @val 1 5 using chars
declare @val varbinary(16) select @val = textptr(copy) from blurbs readpast where au_id = "648-92-1872" readtext blurbs.copy @val 1 5 readpast using chars
The textptr function returns a 16-byte binary string (text pointer) to the text or image column in the specified row or to the text or image column in the last row returned by the query, if more than one row is returned. It is best to declare a local variable to hold the text pointer, then use the variable with readtext.
The value in the global variable @@textsize, which is the limit on the number of bytes of data to be returned, supersedes the size specified for readtext if it is less than that size. Use set textsize to change the value of @@textsize.
When using bytes as the offset and size, Adaptive Server may find partial characters at the beginning or end of the text data to be returned. If it does, and character set conversion is on, the server replaces each partial character with a question mark (?) before returning the text to the client.
Adaptive Server must determine the number of bytes to send to the client in response to a readtext command. When the offset and size are in bytes, determining the number of bytes in the returned text is simple. When the offset and size are in characters, the server must calculate the number of bytes being returned to the client. As a result, performance may be slower when using characters as the offset and size. The using characters option is useful only when Adaptive Server is using a multibyte character set: it ensures that readtext will not return partial characters.
You cannot use readtext on text and image columns in views.
If you attempt to use readtext on text values after changing to a multibyte character set, and you have not run dbcc fix_text, the command fails, and an error message instructs you to run dbcc fix_text on the table.
readpast applies only to data-only-locked tables. readpast is ignored if it is specified for an allpages-locked table.
The readpast option is incompatible with the holdlock option. If both are specified in a command, an error is generated and the command terminates.
If readtext specifies at isolation read uncommitted, readpast generates a warning, but does not terminate the command.
If the statement isolation level is set to 3, readpast generates an error and terminates the command.
If the session-wide isolation level is 3, readpast is silently ignored.
If the session-wide isolation level is 0, readpast generates a warning, but does not terminate the command.
SQL92 – Compliance level: Transact-SQL extension.
readtext requires select permission on the table. readtext permission is transferred when select permission is transferred.
System procedures text and image datatypes
