readtext

Description

Reads text, unitext, and image values, starting from a specified offset and reading a specified number of bytes or characters.

Syntax

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]}]

Parameters

table_name.column_name

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.

text_pointer

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.

offset

specifies the number of bytes or characters to skip before starting to read text, unitext, or image data.

size

specifies the number of bytes or characters of data to 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.

noholdlock

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.

readpast

specifies that readtext should silently skip rows with exclusive locks, without waiting and without generating a message.

using

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.

at isolation

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.

read uncommitted

specifies isolation level 0 for the query. You can specify 0 instead of read uncommitted with the at isolation clause.

read committed

specifies isolation level 1 for the query. You can specify 1 instead of read committed with the at isolation clause.

repeatable read

specifies isolation level 2 for the query. You can specify 2 instead of serializable with the at isolation clause.

serializable

specifies isolation level 3 for the query. You can specify 3 instead of serializable with the at isolation clause.

Examples

Example 1

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

Example 2

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

Usage


Using readtext with unitext columns

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.


Using the readpast option

Standards

ANSI SQL – Compliance level: Transact-SQL extension.

Permissions

readtext requires select permission on the table. readtext permission is transferred when select permission is transferred.

See also

Commands set, writetext

System procedures text, image, and unitext datatypes