![LIKE predicate for character large object (CLOB) columns [CR 415546]](arrow-left.gif){kind=icon}
![Using query plans with Interactive SQL for Java [338070]](arrow-right.gif){kind=icon}
Sybase IQ 12.7 ESD #3 provides string search capabilities on large objects. Sybase IQ now includes support of string search functions for LONG VARCHAR (CLOB) and LONG BINARY (BLOB) data. The string search functions CHARINDEX and LOCATE have been enhanced to allow a LONG VARCHAR or a LONG BINARY column as the argument for the string expression to be searched and now return a 64 bit value for the position of the string in the LONG VARCHAR or LONG BINARY column.
The PATINDEX function has been enhanced to allow a LONG VARCHAR column as the argument for the string expression to be searched and now returns a 64 bit value for the position of the string in the LONG VARCHAR column.
Users must be specifically licensed to use the Large Objects Management functionality. For details on the Large Objects Management option, see Large Objects Management in Sybase IQ.
See “Documentation updates and clarifications” for documentation changes related to this feature.
The following sections describe the string search functions that support LONG VARCHAR and LONG BINARY data types.
The CHARINDEX function returns a 64 bit signed integer containing the position of the first occurrence of the specified string in a LONG VARCHAR column. CHARINDEX returns a 32 bit signed integer position for CHAR and VARCHAR columns.
The CHARINDEX function also supports searching LONG BINARY columns.
Syntax:
CHARINDEX( string-expression, long-varchar-column )
Parameters:
string-expression The string for which you are searching. This string is limited to 255 bytes.
long-varchar-column The name of the LONG VARCHAR column.
Usage:
All the positions or offsets, returned or specified, in the CHARINDEX function are always character offsets and may be different from the byte offset for multibyte data.
If the LONG VARCHAR cell being searched contains more than one instance of the string, CHARINDEX returns only the position of the first instance.
If the column does not contain the string, the CHARINDEX function returns zero (0).
Searching for a string longer than 255 bytes returns NULL.
Searching for a zero-length string returns 1.
If any of the arguments is NULL, the result is NULL.
For a full description and an example of the CHARINDEX function, see “CHARINDEX function [String]” in Chapter 5, “SQL Functions” of the Sybase IQ Reference Manual.
The LOCATE function returns a 64 bit signed integer containing the position of the specified string in a LONG VARCHAR column. LOCATE returns a 32 bit signed integer position for CHAR and VARCHAR columns.
The LOCATE function also supports searching LONG BINARY columns.
Syntax:
LOCATE( long-varchar-column, string-expression [, numeric-expression ] )
Parameters:
long-varchar-column The name of the LONG VARCHAR column to search.
string-expression The string for which you are searching. This string is limited to 255 bytes.
numeric-expression The character position or offset at which to begin the search in the string. The numeric-expression is a 64 bit signed integer for LONG VARCHAR and LONG BINARY columns and is a 32 bit signed integer for CHAR, VARCHAR, and BINARY columns. The first character is position 1. If the starting offset is negative, LOCATE returns the last matching string offset, rather than the first. A negative offset indicates how much of the end of the string to exclude from the search. The number of characters excluded is calculated as ( -1 * offset ) - 1.
Usage:
All the positions or offsets, returned or specified, in the LOCATE function are always character offsets and may be different from the byte offset for multibyte data.
If the LONG VARCHAR cell being searched contains more than one instance of the string:
If numeric-expression is specified, LOCATE starts the search at that offset in the string.
If numeric-expression is not specified, LOCATE returns only the position of the first instance.
If the column does not contain the string, the LOCATE function returns zero (0).
Searching for a string longer than 255 bytes returns NULL.
Searching for a zero-length string returns 1.
If any of the arguments is NULL, the result is NULL.
For a full description and examples of the LOCATE function, see “LOCATE function [String]” in Chapter 5, “SQL Functions” of the Sybase IQ Reference Manual.
The PATINDEX function returns a 64 bit unsigned integer containing the position of the first occurrence of the specified pattern in a LONG VARCHAR column. PATINDEX returns a 32 bit unsigned integer position for CHAR and VARCHAR columns.
The PATINDEX function does not support searching LONG BINARY columns.
Syntax:
PATINDEX( ‘%pattern%’, long-varchar-column )
Parameters:
pattern The pattern for which you are searching. This string is limited to 126 bytes. If the leading percent wildcard is omitted, PATINDEX returns one (1) if the pattern occurs at the beginning of the column value, and zero (0) if the pattern does not occur at the beginning of the column value. Similarly, if the trailing percent wildcard is omitted, the pattern should occur at the end of the column value. The pattern uses the same wildcards as the LIKE comparison.
long-varchar-column The name of the LONG VARCHAR column.
Usage:
All the positions or offsets, returned or specified, in the PATINDEX function are always character offsets and may be different from the byte offset for multibyte data.
If the LONG VARCHAR cell being searched contains more than one instance of the string pattern, PATINDEX returns only the position of the first instance.
If the column does not contain the pattern, the PATINDEX function returns zero (0).
Searching for a pattern longer than 126 bytes returns NULL.
Searching for a zero-length pattern returns 1.
If any of the arguments is NULL, the result is zero (0).
For a full description and examples of the PATINDEX function, see “PATINDEX function [String]” in Chapter 5, “SQL Functions” of the Sybase IQ Reference Manual.
For more information on LIKE comparisons, see “LIKE conditions” in Chapter 3, “SQL Language Elements” of the Sybase IQ Reference Manual.
For information on a known issue related to the PATINDEX function, see “PATINDEX returns 0 for zero-length search string [CR 475209]”.
