Operator precedence is now applied   Previously, no precedence was applied to operators in a query string. Now, the following operator precedence is applied:

• NEAR, FUZZY operators
• AND NOT operator
• AND operator
• OR operator

See Operator precedence in a CONTAINS search condition.

NEAR clause arguments must be terms or prefix terms   When you perform a proximity search, the arguments to the NEAR clause must be terms or prefix terms. See CONTAINS search condition, and Proximity searching.

Use of hyphen and AND NOT clause   Within a phrase, a hyphen is treated as a term breaker, not a special character. Outside of a phrase, the treatment of a hyphen depends on the syntax surrounding the hyphen. See Allowed syntax for hyphen (-), and Using the AND NOT operator in full text searches.

Use of asterisk and prefix searching   When performing a prefix search, the asterisk must be appended to a term and followed immediately by a space, or by the end of query string, or by one of the allowed special characters. See Allowed syntax for asterisk (*), and Prefix searching.

Creating a duplicate text index now returns an error   You can no longer create duplicate text indexes. A text index is considered a duplicate if the following settings are identical to those of an existing text index:

Duplicate text indexes created before SQL Anywhere 11.0.1 can remain in the database and do not cause errors when started with a 11.0.1 database server. However, if a database that contains duplicate text indexes is reloaded on version 11.0.1 or later, an error is returned.

To identify duplicate text indexes in an existing database, execute the following query:

SELECT LIST( i.index_name )
FROM SYS.SYSIDX i 
    JOIN SYS.SYSTEXTIDX t ON i.object_id = t.index_id AND t.sequence = 1
    JOIN SYS.SYSTEXTCONFIG F ON F.object_id = t.text_config
    JOIN ( 
	SELECT table_id, index_id, LIST( column_id, ', ' ORDER BY column_id ) col_id
	FROM SYS.SYSIDXCOL
	GROUP BY table_id, index_id) x 
	ON x.table_id = i.table_id AND x.index_id = i.index_id
WHERE i.index_category=4
GROUP BY i.table_id, f.term_breaker, f.min_term_length, f.max_term_length, 
    f.collation, ISNULL( f.char_stoplist, '-' ), 
    ISNULL( f.nchar_stoplist, '-' ), x.col_id
HAVING count(*) > 1

This query works only if the strings representing STOPLIST are exactly the same or if NO STOPLIST is specified. For example, stoplists 'a b c' and 'a - b c' are not considered the same stoplist by this query, but would be considered the same during a check for duplicates during text index creation.

Database collations and matching   Previously, REGEXP and REGEXP_SUBSTR determined if a literal or character class range in the pattern matched the string by using the collation equivalence and sort order. Now, REGEXP and REGEXP_SUBSTR use binary comparisons of code point values for matching and evaluation of ranges. The change was made to make the behavior consistent with Perl 5.0.

SIMILAR TO still uses the database collation for matching and range evaluation. See LIKE, REGEXP, and SIMILAR TO search conditions.

Database case sensitivity and [[:upper:]] and [[:lower:]] sub-character classes   SIMILAR TO and REGEXP [[:upper:]] and [[:lower:]] sub-character classes were case-insensitive on a case-insensitive database. This has been changed so that [[:upper:]] only matches upper case characters and [[:lower:]] only matches lower case characters, regardless of the database case sensitivity.

Treatment of caret (^), underscore (_), and percent sign (%) as metacharacters   The following table explains the previous and new treatment of these characters as metacharacters: