COMPARE function

The COMPARE function compares two strings and lets you:

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