Text functions used for text and image data

Text built-in functions are used for operations on text and image data. Table 10-5 lists text function names, arguments, and results:

Built-in text functions for text and image data

Function

Argument

Result

patindex

("%pattern%", char_expr [using {bytes | chars | characters} ] )

Returns an integer value representing the starting position of the first occurrence of pattern in the specified character expression; returns 0 if pattern is not found. By default, patindex returns the offset in characters; to return the offset in bytes for multibyte character strings, specify using bytes. The % wildcard character must precede and follow pattern, except when you are searching for first or last characters. See "Character strings in query results" for a description of the wildcard characters that can be used in pattern.

textptr

(text_columname)

Returns the text pointer value, a 16-byte varbinary value.

textvalid

("table_name..col_name", textpointer)

Checks that a given text pointer is valid. Note that the identifier for a text or image column must include the table name. Returns 1 if the pointer is valid, 0 if the pointer is invalid.

datalength also works on text columns. See "System functions that return database information" for information about datalength.

The set textsize command specifies the limit, in bytes, of the text or image data to be returned with a select statement. For example, this command sets the limit on text or image data returned with a select statement to 100 bytes:

set textsize 100

The current setting is stored in the @@textsize global variable. The default setting is controlled by the client program. To reset the default, issue:

set textsize 0

You can also use the @@textcolid, @@textdbid, @@textobjid, @@textptr, and @@textsize global variables to manipulate text and image data.

Readtext

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.

The full syntax of readtext is: 

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

The holdlock flag locks the text value for reads until the end of the transaction. Other users can read the value but cannot modify it. The at isolation clause is described in Chapter 18, "Transactions: Maintaining Data Consistency and Recovery."

If you are using a multibyte character set, the using option allows you to choose whether you want readtext to interpret the offset and size as bytes or as characters. Both chars and characters specify characters. This option has no effect when used with a single-byte character set or with image values (readtext reads image values only on a byte-by-byte basis). If the using option is not given, readtext returns the value as if bytes were specified.

Adaptive Server has to 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, Adaptive 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. using characters is useful only when Adaptive Server is using a multibyte character set. This option ensures that readtext does not return partial characters.

When using bytes as the offset, Adaptive Server may find partial characters at the beginning or end of the text data to be returned. If it does, the server replaces each partial character with question marks before returning the text to the client.

You cannot use readtext on text and image columns in views.

Examples of using text functions

This example uses the textptr function to locate the text column, copy, associated with title_id BU7832 in table blurbs. The text pointer, a 16-byte binary string, is put into a local variable, @val, and supplied as a parameter to the readtext command. readtext returns 5 bytes starting at the second byte, with an offset of 1. 

declare @val binary(16) 
select @val = textptr(copy) from blurbs 
where au_id = "486-29-1786" 
readtext blurbs.copy @val 1 5

textptr returns a 16-byte varbinary string. Sybase suggests that you put this string into a local variable, as in the preceding example, and use it by reference.

An alternative to using textptr in the preceding declare example is the @@textptr global variable:

readtext texttest.blurb @@textptr 1 5

The value of @@textptr is set from the last insert or update to any text or image field by the current Adaptive Server process. Inserts and updates by other processes do not affect the current process.

Explicit conversion using the convert function is supported from text to char, nchar, unichar, varchar, univarchar, or nvarchar, and from image to varbinary or binary, but text or image data is truncated to pagesize. Conversion of text or image to datatypes other than these is not supported, implicitly or explicitly.