COMPARE function

The COMPARE function compares two strings and lets you:

Perform a raw compare, if desired

Use a particular collation

Turn case sensitivity on and off

COMPARE returns a LOGICAL value representing the result of the logical expression, where the comparison rules are defined by the combination of the operator, the comparison strength, and the collation.

Syntax


COMPARE ( `string1` , `relational-operator` , `string2` , `strength` [ , `collation` ] )

string1

A CHARACTER or LONGCHAR expression that evaluates to the first string to be compared.

relational-operator

A CHARACTER expression that evaluates to one of the relational operators, which are: LT (or <), LE (or <=), EQ (or =), GE (or >=), GT (or >), NE (or <>), “BEGINS”, and “MATCHES”.

string2

A CHARACTER or LONGCHAR expression that evaluates to the second string to be compared.

strength

A CHARACTER expression that evaluates to the ABL comparison strength or the International Components for Unicode (ICU) comparison strength to apply.

The ABL comparison strengths include:

RAW — The AVM compares the two strings using the numeric values in the current code page.

CASE-SENSITIVE — The AVM performs a case-sensitive comparison of the two strings using the numeric values in either the collation table specified in collation, or the collation table of the client. If you specify this strength with an ICU collation, the AVM applies the ICU TERTIARY strength.

CASE-INSENSITIVE — The AVM performs a case-insensitive comparison of the two strings using the numeric values in either the collation table specified in collation, or the collation table of the client. If you specify this strength with an ICU collation, the AVM applies the ICU SECONDARY strength.

CAPS — The AVM converts any lowercase letters in the two strings to uppercase letters, based on the settings of the Internal Code Page (-cpinternal) and Case Table (-cpcase) startup parameters, and then performs a raw comparison of the resulting strings. When neither string contains a wildcard character, this option behaves the same as the MATCHES operator.

The ICU comparison strengths include:

PRIMARY — The AVM compares the base characters in the two strings.

SECONDARY — The AVM compares the base characters and any diacritical marks in the two strings.

TERTIARY — The AVM performs a case-sensitive comparison of the base characters and diacritical marks in the two strings.

QUATERNARY — The AVM performs a case-sensitive comparison of the base characters and any diacritical marks in the two strings, and distinguishes words with and without punctuation. ICU uses this strength to distinguish between Hiragana and Katakana when applied with the ICU-JA (Japanese) collation. Otherwise, it is the same as TERTIARY.

Note: Use ICU comparison strengths only with ICU collations.

collation

A CHARACTER expression that evaluates to the name of an ABL collation table or ICU collation. By default, the AVM uses the collation rules you specify to compare characters and sort records. The collation rules specified with the Collation Table (-cpcoll) startup parameter take precedence over a collation specified for any database the AVM accesses during the session, except when the AVM uses or modifies pre-existing indexes. If you do not specify a collation with the -cpcoll startup parameter, the AVM uses the language collation rules defined for the first database on the command line. If you do not specify a database on the command line, the AVM uses the collation rules with the default name "basic" (which might or might not exist in the convmap.cp file).

If strength is not RAW or CAPS, the collation must be either an ABL collation table in the convmap.cp file or an ICU collation, and must be a valid collation table for the code page corresponding to the -cpinternal startup parameter.

Notes

If either or both strings evaluate to the Unknown value (?), COMPARE returns the value indicated in Table 15.

Table 15: Relational operators and the Unknown value (`?`)
Relational operator	Only one string evaluates to ?	Both strings evaluate to ?
LT (or <)	FALSE	FALSE
LE (or <=)	FALSE	TRUE
EQ (or =)	FALSE	TRUE
GE (or >=)	FALSE	TRUE
GT (or >)	FALSE	FALSE
NE (or <>)	TRUE	FALSE
BEGINS	FALSE	TRUE
MATCHES	FALSE	TRUE

COMPARE returns the Unknown value (?) if one of the following occurs:

relational-operator does not evaluate to a valid value.

strength does not evaluate to a valid value.

collation does not evaluate to a collation table residing in the convmap.cp file.

collation evaluates to a collation table that is not defined for the code page corresponding to the -cpinternal startup parameter.

LONGCHAR variable values are converted to -cpinternal for comparison and must convert without error, or the AVM returns an error.

With BEGINS, the language-sensitive rules are used only when strength is not RAW or CAPS.

With MATCHES, CASE-SENSITIVE is treated as RAW, CASE-INSENSITIVE is treated as CAPS, and the collation is never used.