FrontBase Documentation

Backtrack:
  Welcome!
    5. SQL 92
      5.1. SQL 92 Primer
Updated: 20-Nov-2000
prev — next
Table of Contents

5.1.4. 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, thus supporting all the so-called international characters. However, the positional values of the international characters in the Unicode universe can not be used for ordering two characters if the ordering is to turn out as most people expect it.
An example: The French character 'ç'(Latin lower case 'c' with cedilla) has ordinal value 231 (decimal) while a lower case 'c' has ordinal value 99. If 'ç' and 'd' were compared, 'd' would be "less than" 'ç', which would probably not be the desired result.

Case-Insensitive Compare Operations
Character strings are usually stored in the database using the same case as they were entered by a user. Some users prefer to enter just lower case characters, while other prefer upper case characters, and a few insist on using mixed-capitalization (e.g. "FrontBase"). When searching, users generally don't know in which case characters were entered. A search has to account for this. Case-insensitive searches can be handled using a SQL 92 statement such as:

SELECT * FROM T0 WHERE UPPER(CITY) = 'COPENHAGEN';

The problem with above SELECT is that an index defined on T0.CITY cannot be used. The SELECT will execute slower than it would if an index could be used.
By defining a COLLATION, you can specify how characters are to be ordered, mapping the ordinal values to ordering values. For example, if 'a' is mapped to the same ordering value as 'A', then 'a'will be considered equal to 'A'.
Included with the FrontBase distribution is a collation table called CaseInsensitive.coll1 (located in the FrontBase/Collations directory). As implied by its name, this collation table can be used for case-insensitive comparisons. First you need to define the COLLATION with a SQL 92 statement:

CREATE COLLATION CASE_INSENSITIVE     FOR INFORMATION_SCHEMA.SQL_TEXT     FROM EXTERNAL('CaseInsensitive.coll1');

COMMIT;

The collation can then be 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 conveniently includes comparisons made 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 you want to make a case-sensitive comparison, you need to define an identity collation and save the collation (e.g. as CaseSensitive.coll1 in the FrontBase/Collations directory. You can then add and use a COLLATION with the following SQL 92 statements:

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. 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? This is easily done:

SELECT DB FROM T0 WHERE     DB = 'FrontBase' AND     DB = 'FrontBase' COLLATE CASE_SENSITIVE;

The first WHERE clause will return {'frontbase', 'FrontBase', 'FRONTBASE'}, while the second WHERE clause 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!!