FrontBase Documentation

Backtrack:
Welcome!
6. Original Documentation
Updated: 20-Nov-2000
prev — next
Table of Contents

6.16. Collations

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 characters
2) Case insensitive compare operations
International Characters

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.

An example:
   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 rows
If 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 rows
Please 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 doc-feedback@frontbase.com. Please reference the section number and topic. Thanks!!