A summary of function support of large object data types and variables.
The table "Function Support of LOB Data Types and Variables" 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 listed in this table, you can use the BFILE function to extract LOB data. See Large Object Data Exports.
Scalar and aggregate user-defined functions support large object data types as input parameters. See User-Defined Function Support of Large Object Columns.
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.
For full descriptions of these functions and examples, see Reference: Building Blocks, Tables, and Procedures > SQL Functions.