String comparisons on encrypted text [CR 436993]

If data is case insensitive, or uses a collation other than ISO_BINENG, you must decrypt ciphertext columns in order to perform string comparisons.

When performing comparisons on strings, the distinction between equal and identical strings is important for many collations and depends on CASE option of CREATE DATABASE. In a database set to CASE RESPECT and the ISO_BINENG collation, the defaults for Sybase IQ, equality, and identity questions are resolved the same way.

Identical strings are always equal, but the reverse may not be true. Strings are only identical if they are represented using the same byte values. When data is case insensitive or uses a collation where multiple characters must be treated as equal, the distinction between equality and identity is significant. ISO1LATIN1 is such a collation.

For example, the strings 'ABC' and 'abc' in a case insensitive database are not identical but are equal. In a case sensitive database, they are neither identical nor equal.

The ciphertext created by the Sybase encryption functions preserves identity but not equality. In other words, the ciphertext for 'ABC' and 'abc' will never be equal.