Collations define how strings are sorted and compared. TurboDB uses a similar collation naming schema as Microsoft SQL Server. A collation name consists of a Windows locale name plus two or four characters that indicate, whether the string is case sensitive and diacritics sensitive. The meaning is:
| • | AS: Sensitive for diacritics (accents) |
| • | AI: Insensitive to diacritics (accents) |
If both are given the case specification must precede the diacritics specification. Each specification can be omitted, in which case the diacritics sensitivity defaults to true and the case sensitivity defaults to false. In addition to the Windows locale names, the special collation TurboDB indicates the sorting of TurboDB 5 and before. Therefore you cannot append the as/ai/cs/ci specifications to the collation name TurboDB. Collation names themselves are case insensitive.
The following are some examples of valid TurboDB collation names:
| • | German (diacritics sensitive, case insensitive) |
| • | English_ai (diacritics insensitive, case insensitive) |
| • | Spanish_ci_as (diacritics sensitive, case insensitive) |
| • | Russian_cs (diacritics sensitive, case sensitive) |
| • | Russian_CS (same as above) |
| • | TurboDB (diacritics sensitive, case insensitive) |
These are examples for invalid collation names:
| • | Collation1_ai (Collation1 is not a Windows locale) |
| • | Spanish_as_ci (Wrong order of sensitivity specifications) |
| • | Spanish_ca (Invalid sensitivity specification) |
| • | TurboDB_cs (Special collation TurboDB cannot have additional specifications) |
Tip: Use the table properties window of TurboDB Viewer to display a list of collations available on your system.
Collations can be assigned and checked in different ways:
| • | TurboDB Viewer displays collations and allows to choose one, when creating or altering a table. |
| • | TurboSQL supports the COLLATE clause for column data types. |
| • | In the VCL library the TTdbFieldDef class has a property named Specification, which takes the collation name for string types. |
| • | The .NET providers support collation names through the appropriate ADO.NET interfaces. |
Compatibility
Collations are supported as of TurboDB Win v6 and TurboDB Managed v3. Only tables of level 6 and higher allow the collation definition on a per column basis. Tables of a lower level allow the collation definition on a per table basis using the three letter ISO code for the language. Earlier versions of TurboDB used language drivers, which a no more supported. See the upgrade instructions to learn how databases can be migrated.
Because string comparison is now 100% consistent in filters, SQL, TurboPL and indexes, the comparison in older TurboDB tables is now case insensitive. That means that MyStringColumn = 'TestString' is true for a MyStringColumn value of 'teststring' in TurboDB Win 6, whereas it was false in TurboDB Win 5 and below. If you want the case sensitive comparison, define another collation for the table or for the column.
See also
CREATE TABLE statement