Consultor Eletrônico

Status: Unverified

SYMPTOM(s):

Data contains German characters, such as Umlauts

Records with German characters are not found when using BEGINS

FOR EACH <table> WHERE <field> BEGINS "O" NO-LOCK returns records beginning "O" but not "Ö"

Ober
Otto

FOR EACH <table> NO-LOCK returns both records beginning "O" and record beginning "Ö"

Ober
Öblarn
Otto

Collation is Basic9

COMPARE statement returns True for "O" and "Ö"

COMPARE("Ö", "=", "O", "case-insensitive", "basic9")

No index is defined on the field containing the data with German characters

Defining a Primary index on the field containing the data with German characters results in the correct records being returned

CAUSE:

The BEGINS statement behaves differently for indexed searches vs non-indexed searches. In short, indexes use collation tables which make accented equal to non-accented characters. However, to search a non-indexed field Progress uses an algorithm of uppercasing characters and then performing a binary compare. Since the binary value of an upper accented character is not the same as that of a different character, this is case-insensitive but accent-sensitive.

FIX:

Option #1
Use the COMPARE station in place of the BEGINS statement in the query. For example:

FOR EACH <table> WHERE COMPARE(<field>,"BEGINS","O","case-insensitive","basic9") NO-LOCK

The COMPARE statement can take a BEGINS operator, which will honour the collation and return the correct results when used in a WHERE clause. As this is a non-indexed field, using COMPARE will have similar performance impact to using BEGINS (i.e. it has to be evaluated instead of using an index).

Option #2
Define appropriate indexes in the database