Text built-in functions are used for operations on text, image, and unitext data. Table 16-5 lists text function names, arguments, and results:
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. The identifier for a text, unitext, or image column must include the table name. Returns 1 if the pointer is valid, 0 if the pointer is invalid. |
See the “Usage” section of each function
in the Reference Manual: Building Blocks for
restrictions when using these functions on unitext data.
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, image, and unitext data to be returned with a select statement. For example, this command sets the limit on text, image, and unitext 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, unitext, and image data.
