Home

	Expand Search

Transact-SQL User's Guide

Chapter 2 Queries: Selecting Data from a Table

Choosing columns: the select clause

Selecting text and image values

Selecting text and image values

text and image values can be quite large. When a select list includes text and image values, the limit on the length of the data returned depends on the setting of the @@textsize global variable. The default setting for @@textsize depends on the software you use to access Adaptive Server; the default value is 32K for isql. To change the value, use the set command:

set textsize 25

With this setting of @@textsize, a select statement that includes a text column displays only the first 25 bytes of the data.

When you select image data, the returned value includes the characters "0x", which indicates that the data is hexadecimal. These two characters are counted as part of @@textsize.

To reset @@textsize to the Adaptive Server default value, use:

set textsize 0 

The default display is the actual length of the data when its size is less than textsize. For more information about text and image datatypes, see Chapter 6, "Using and Creating Datatypes."

The readtext command provides a way to retrieve text and image values if you want to retrieve only a selected portion of a column's data. readtext requires the name of the table and column, the text pointer, a starting offset within the column, and the number of characters or bytes to retrieve. This example finds six characters in the copy column in the blurbs table:

declare @val binary(16) 
select @val = textptr(copy) from blurbs 
where au_id = "648-92-1872" 
readtext blurbs.copy @val 2 6 using chars

In the example, after the @val local variable has been declared, readtext displays characters 3 - 8 of the copy column, since the offset was 2.

Instead of storing potentially large text and image data in the table, Adaptive Server stores it in a special structure. A text pointer (textptr) which points to the page where the data is actually stored is assigned. When you retrieve data using readtext, you actually retrieve textptr, which is a 16-byte varbinary string. To avoid this, declare a local variable to hold textptr, and then use the variable with readtext, as in the example above.

See "Text functions used for text and image data" for an advanced discussion of the readtext command.