Sorting and searching non-English text can cause a number of problems, a frequent one being how to handle accented letters, for example á, à and â.
The rules for sorting vary because the various natural languages sort words differently. There are occasions where the accented form of a letter is treated as a distinct letter for the purpose of comparison. For example, in Swedish, Å is a separate letter that is sorted after Z. In some languages, it is common to sort uppercase before lowercase, in other languages this is reversed; sometimes it is just a matter of personal preference.
A collation, also known as a collating sequence, is a database object containing a set of rules that determines how character strings are compared, searched and alphabetically sorted. The rules in the collation determine whether one character string is less than, equal to or greater than another. A collation also determines how case-sensitivity and accents are handled.
In Mimer SQL, a collation belongs to a SCHEMA. In this release, the pre-defined collations included belong to INFORMATION_SCHEMA.
When a collation is used, Mimer SQL first checks to see if it belongs to the ident's schema. If Mimer SQL does not find it there, it checks for it in INFORMATION_SCHEMA.
Character Sets and Collations
For character data, Mimer SQL uses the character set ISO 8859-1, also known as the LATIN1 character set. By default, character data is sorted in the numerical order of their codes according to the ISO8BIT collation.
For national character data, Mimer SQL uses the Unicode character set. National character data is sorted according to the Unicode Collation Algorithm.
Each collation belongs to a character set.
A collation belonging to a particular character set can only be used to compare or order character values belonging to that character set
Every character set has one default collation.
ISO8BIT swedish - Swedish language-specific sorting rules, case insensitive and accent insensitive.english - English language-specific sorting rules, case insensitive and accent insensitive. Unicode Collation Algorithm N/A
You can review copies of these collations by opening the collations.sql file included in your Mimer SQL distribution.
Note: Upright Database Technology AB reserves the right to change the contents of these collations in future releases.
See the Mimer SQL Reference Manual, Appendix B, Character Sets for more information.
If you want to sort characters in a different way than the default, you can specify a collation at the column level when creating or altering a table or creating a domain. You can also override a collation by using a COLLATE clause in an SQL statement.
Who can use collations?
Currently, anyone can use a collation.
Upright Database Technology AB
Voice: +46 18 780 92 00
Fax: +46 18 780 92 40