Reads text, unitext, 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, unitext, 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, unitext, or image data. Use the textptr function to determine this value. text, unitext, 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, unitext, 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, unitext, or image column in the specified row or to the text, unitext, or image column in the last row returned by the query, if more than one row is returned. 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 does not return partial characters.
You cannot use readtext on text, unitext, or 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.
When you issue readtext on a column defined for the unitext datatype, the readtext offset parameter specifies the number of bytes, or Unicode values, to skip before starting to read the unitext data. The readtext size parameter specifies the number of bytes, or 16-bit Unicode values, to read. If you specify using bytes (the default), the offset and size values are adjusted to always start and end on the Unicode character boundaries, if necessary.
If enable surrogate processing is on, readtext truncates only on the surrogate boundary, and starting/ending positions are also adjusted accordingly and returns whole Unicode characters. For this reason, issuing readtext against a column defined for unitext may return fewer bytes than specified.
In the following example, the unitext column ut includes the string U+0101U+0041U+0042U+0043:
declare @val varbinary (16) select @val = textptr (ut) from unitable where i = 1 readtext foo.ut @val 1 5
This query returns the value U+0041U+0042.
The offset position is adjusted to 2 since readtext cannot start from the second byte of a Unicode character. Unicode characters are always composed of an even number of bytes. Starting at the second byte (or ending in an odd number of bytes) shifts the result by one byte, and renders the result set inaccurate.
In the example above, the size value is adjusted to 4 since readtext cannot read the partial byte of the fourth character, U+0043.
In the following query, enable surrogate processing is enabled, and the ut column contains the string U+d800dc00U+00c2U+dbffdeffU+d800dc00:
declare @val varbinary (16) select @val = textptr (ut) from unitable where i = 2 readtext foo.ut @val 1 8
This query returns the value U+00c2U+dbffdeff. The starting position is reset to 2, and the actual result size is 6 bytes rather than 8 since readtext does not break in the middle of a surrogate pair. Surrogate pairs (in this example, the first value in the range d800..dbff and the second in the range dc00..dfff) require 4-byte boundaries, and the rules of Unicode conformance for UTF-16 do not allow the division of these 4-byte characters.
readpast applies only to data-only-locked tables, and is ignored if it is specified for an allpages-locked table.
readpast 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.
ANSI SQL – Compliance level: Transact-SQL extension.
readtext requires select permission on the table.
System procedures text, image, and unitext datatypes
