In WHERE clauses of the SELECT statement, you can use LONG VARCHAR columns only in IS NULL and IS NOT NULL expressions, in addition to the BIT_LENGTH, CHAR_LENGTH, CHAR_LENGTH64, CHARINDEX, LOCATE, OCTET_LENGTH, PATINDEX, SUBSTRING64, and SUBSTRING functions.
You can use the LIKE predicate to search for a pattern on a LONG VARCHAR column. All patterns of 126 or fewer characters are supported. Patterns longer than 254 characters are not supported. Some patterns between 127 and 254 characters in length are supported, depending on the contents of the pattern.
The LIKE predicate supports LONG VARCHAR (CLOB) variables of any size of data. Currently, a SQL variable can hold up to 2GB - 1 in length.
You cannot use LONG VARCHAR columns in the SELECT statement clauses ORDER BY, GROUP BY, and HAVING or with the DISTINCT keyword (SELECT DISTINCT and COUNT DISTINCT).
See Chapter 9, “Function Support.”
You can create a WORD (WD) index on a LONG VARCHAR (CLOB) column and use the CONTAINS predicate to search the column for string constants of maximum length 255 characters.
The CONTAINS predicate is not supported on LONG BINARY (BLOB) columns using WD indexes. If you attempt to search for a string in a LONG BINARY column with a WD index using a CONTAINS predicate, an error is returned. TEXT indexes that use an external library support CONTAINS on binary data.
See “CONTAINS conditions” in Chapter 2, “SQL Language Elements” in Reference: Building Blocks, Tables, and Procedures.
