Consultor Eletrônico

BEGINS does not always use collation tables

The actual Progress products including version 8 do not provide
collation support for some phrases/statements: BEGINS, MATCHES,
LOOKUP, ENTRY, CAN-DO, RINDEX and INDEX. This functions do
comparisons by uppercaseing and comparing character encodings rather
than by comparing collation weights,

Among them, BEGINS is the only one which uses indexes when possible.
This makes that, when an index is used, BEGINS seems to use collation
tables because the index feeds the BEGINS with the data already
collated.

For example, this code will return all customers which names begin
with letters "a" and "á" because there is an index on name:

FOR EACH customer WHERE name BEGINS "a":

However, the following code will return only the customers which
addresses begin with "a" (and not with "á") because there is no index
on the field address:

FOR EACH customer WHERE address BEGINS "a":

Combining two BEGINS phrases, we can get strange results depending on
where the indexes are used and applied. For example, this code:

FOR EACH customer WHERE name BEGINS "a" OR
name BEGINS "e":

will NOT return the customers which names begin with "á" nor "é".

When more than one BEGINS has to be used in the same WHERE clause, or
when BEGINS has to be used on a non indexed field, it is better to
use the SUBSTRING function to resolve the query. The required
functionality for the previous example can be achieved with the
following code:

FOR EACH customer WHERE SUBSTRING(name,1,1) = "a" OR
SUBSTRING(name,1,1) = "e":


jsa
7 Jan 98


Progress Software Technical Support Note # 17539