The Table 9-1 summarizes the function support of LONG BINARY (BLOB) and LONG VARCHAR (CLOB) data types and LONG BINARY and LONG VARCHAR variables.
In addition to the functions described in this chapter, you can use the BFILE function to extract LOB data. See “Exporting large object data”.
Function |
BLOB data supported? |
BLOB variables supported? |
CLOB data supported? |
CLOB variables supported? |
|---|---|---|---|---|
BIT_LENGTH() |
yes |
yes |
yes |
yes |
BYTE_LENGTH() |
yes* |
yes* |
yes* |
yes* |
BYTE_LENGTH64() |
yes |
yes |
yes |
yes |
BYTE_SUBSTR() |
yes |
yes |
yes |
yes |
BYTE_SUBSTR64() |
yes |
yes |
yes |
yes |
CHAR_LENGTH() |
no |
no |
yes |
yes |
CHAR_LENGTH64() |
no |
no |
yes |
yes |
CHARINDEX() |
yes |
yes |
yes |
yes |
LOCATE() |
yes |
yes |
yes |
yes |
OCTET_LENGTH() |
yes |
yes |
yes |
yes |
PATINDEX() |
no |
no |
yes |
yes |
SUBSTR() / SUBSTRING() |
no |
no |
yes |
yes |
SUBSTRING64() |
yes |
yes |
yes |
yes |
*The BYTE_LENGTH function supports both LONG BINARY columns and variables and LONG VARCHAR columns and variables, only if the query returns less than 2GB. If the byte length of the returned LONG BINARY or LONG VARCHAR data is greater than 2GB, BYTE_LENGTH returns an error that says you must use the BYTE_LENGTH64 function.
The following sections describe the functions that support LONG BINARY and LONG VARCHAR columns and variables. For full descriptions of these functions and examples, see Chapter 4, “SQL Functions” of Reference: Building Blocks, Tables, and Procedures.
