In WHERE clauses of the SELECT statement, LONG VARCHAR columns can only be used 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 characters or less are supported. Patterns of length greater than 254 characters are not supported. Some patterns of length between 127 and 254 characters are supported, depending on the contents of the pattern.
LONG VARCHAR columns cannot be used in the SELECT statement clauses ORDER BY, GROUP BY, and HAVING or with the DISTINCT keyword (SELECT DISTINCT and COUNT DISTINCT).
See “Function support of LONG VARCHAR data type” for more information on LONG VARCHAR data and functions.
The following enhancements support the WORD (WD) index on LONG VARCHAR (CLOB) columns:
Sybase Central Java allows you to create a WD index on columns of CHAR, VARCHAR, and LONG VARCHAR data types.
The widest column supported by the WD index increased from 32767 bytes up to the maximum width for a LOB column. (The maximum length is equal to 4GB multiplied by the database page size.)
The maximum word length supported by Sybase IQ is still 255 bytes.
All sp_iqcheckdb options for WD indexes over CHAR and VARCHAR columns are also supported for LONG VARCHAR (CLOB) columns, including allocation, check, and verify modes.
The sp_iqrebuildindex stored procedure supports rebuilding a WD index over a LONG VARCHAR (CLOB) column.
Note that Chinese text or documents in a binary format still require ETL pre-processing to locate and transform the words into a form that can be parsed by the WD index.
Using the CONTAINS predicate, you can now search for string constants of maximum length 255 characters on a LONG VARCHAR (CLOB) column, in addition to CHAR and VARCHAR columns.
Note that the CONTAINS predicate is not supported on LONG BINARY (BLOB) columns. If you attempt to search for a string in a LONG BINARY column using a CONTAINS predicate, an error is returned.
For more information on CONTAINS string searches, see “CONTAINS conditions” in Chapter 2, “SQL Language Elements” of the Reference: Building Blocks, Tables, and Procedures.
