6. Original Documentation
Table of Contents
Collations are basically a way for you to control how two characters should be compared or rather whether two given characters compare equal, less than or greater than.
Why bother with this?
There are two main reasons for having to bother with collations:
1) International charactersInternational Characters
2) Case insensitive compare operations
FrontBase implements Unicode and support thus use of all the socalled international characters including Kanji, Hangul etc. The positional values of the international characters in the Unicode universe can not be used for ordering two characters, at least if the ordering is to turn out as most people expect it.
An example: The French character ç (Latin Small Letter C With Cedilla) has the ordinal value of 231 (decimal) while a lower case C has 99 as ordinal value. If e.g. ç and d are compared, d would then compare to be smaller than ç, which may not be what you want.
Case Insensitive Compare Operations
Normally character strings are stored in the database using the same case as they were entered by a user. Some users prefer to enter just lower case characters, other prefer upper case characters, and a few uses capitalization as in FrontBase. When searching, users generally don't know in which case characters were entered, i.e. a search has to take care of this.
This can be dealt with by doing something like:
SELECT * FROM T0 WHERE UPPER(CITY) = 'COPENHAGEN';The problem with above SELECT is that an index defined on T0.CITY cannot be used, i.e. the SELECT will execute slower than if an index could be used.
By defining a socalled COLLATION, you can effectively decide how characters are to be ordered, i.e. mapping the ordinal values into ordering values. This means for example that if 'a' is mapped into the same ordering value as 'A', 'a' is considered to be equal to 'A'.
Included with any FrontBase distribution is a collation table called CaseInsensitive.coll1 (located in the <FB home>/Collations directory) and as implied by its name, this collation can be used to do case insensitive compares with.
First you need to define the collation:
CREATE COLLATION CASE_INSENSITIVE FOR INFORMATION_SCHEMA.SQL_TEXT FROM EXTERNAL('CaseInsensitive.coll1'); COMMIT;The collation is then used when creating a table:
CREATE TABLE T0( ... DB VARCHAR(128) COLLATE CASE_INSENSITIVE, ... ); CREATE INDEX ON T0(DB); COMMIT;The specified collation will now automatically be used whenever a DB column value is compared with another string, this includes compares done when building an index.
INSERT INTO T0(DB) VALUES 'frontbase', 'FrontBase', 'FRONTBASE'; COMMIT; SELECT DB FROM T0 WHERE DB = 'FrOnTbAsE'; -- Will return all 3 rows SELECT DB FROM T0 WHERE DB LIKE 'f%'; -- Will return all 3 rowsIf for some reason you want to compare case sensitive, you need to define an identity collation (using the FBUnicodeManager application) and save the collation as e.g. CaseSensitive.coll1 in the <FB home>/Collations directory.
CREATE COLLATION CASE_SENSITIVE FOR INFORMATION_SCHEMA.SQL_TEXT FROM EXTERNAL('CaseSensitive.coll1'); COMMIT; SELECT DB FROM T0 WHERE DB = 'FrontBase' COLLATE CASE_SENSITIVE; -- Will return 1 row SELECT DB FROM T0 WHERE DB LIKE 'F%' COLLATE CASE_SENSITIVE; -- Will return 2 rowsPlease note that the above two SELECTs will not use the index created on column DB, i.e. for large tables these two SELECTs will execute slower than if the index could be used.
Now what if you want to search case insensitive and then limit the result set further by requiring that an exact case match should also apply? Easy done:
SELECT DB FROM T0 WHERE DB = 'FrontBase' AND DB = 'FrontBase' COLLATE CASE_SENSITIVE;The first WHERE component will return 'frontbase', 'FrontBase', 'FRONTBASE', while the second WHERE component will reduce the result to 'FrontBase';
If you have feedback or questions on this document, please send e-mail to email@example.com. Please reference the section number and topic. Thanks!!
©2000 FrontBase, Inc. All rights reserved.