Text functions used for text and image data

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

**Table 11-5: 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.