@Compare (Formula Language)

Syntax

@Compare( textlist ; textlist ; [ options ] )

Parameters

textlist

Text list. The first two parameters are text lists. If one list is shorter, the last element in the shorter list is repeated until it reaches the same length as the longer list. The corresponding elements of each list are compared.

[ options ]

Keyword list. The list can include any of the following keywords. Conflicting options result in the last specified.

[CASESENSITIVE] (default)

[CASEINSENSITIVE]

[ACCENTSENSITIVE] (default)

[ACCENTINSENSITIVE]

[PITCHSENSITIVE] (default)

[PITCHINSENSITIVE]

Return value

result

Number list. Each element is the result of comparing the corresponding elements in the text lists, and is one of three values:

• 0 if the elements in the two lists are equal
• -1 if the element in the first list is less than the element in the second list. For example, this is the result if the first list contains alice and the second list contains bobby.
• 1 if the element in the first list is greater than the element in the second list. For example, this is the result if the first list contains bobby and the second list contains alice.

Usage

The comparison sequence for the English character set is as follows: the apostrophe, the dash, the numbers 0-9, the alphabetic characters a-z and A-Z, and the remaining special characters. The sequence for the alphabetic characters is in order, lowercase character first: a, A, b, B, and so on through z, Z. This sequence can lead to some anomalies; for example, "new york" compares before "New Boston." Use the [CaseInsensitive] option, or @UpperCase, @LowerCase, and @ProperCase to address this behavior.

If you set Unicode standard sorting as the sorting option, you cannot select the following keywords or combinations:

• [PITCHINSENSITIVE]
• [CASESENSITIVE]:[ACCENTINSENSITIVE]

You specify Unicode standard sorting by setting the notes.ini variable $CollationType to @UCA, or by selecting the "Unicode standard sorting" checkbox that displays in the following dialog boxes:

• Sorting dialog box that displays when you choose File - Preferences - User Preferences - International - Sorting from the main menu
• Database Properties box*
• Design Document Properties box*

*The Unicode option is disabled in the Database and Design Document Properties boxes until you select a default sort order.

For more information on Unicode sorting, see http://oss.software.ibm.com/icu/

Examples

1. This action compares a list to the value "N" and displays the result. Boston and Moscow result in -1 (less than N), Tokyo results in 1 (greater than N), and n and N result in 0.

   list := "Boston" : "Tokyo" : "Moscow" : "N" : "n";
   result := @text(@compare(list; "N"; [CaseInsensitive]));
   @Prompt([OKCANCELLIST] : [NOSORT]; 
   "Result"; ""; ""; list + " (" + result + ")")

2. This computed field formula compares the two multi-value fields Name1 and Name2 and posts the result as its value. Text is substituted for the numeric result values.

   @If(Names1 = "" | Names2 = ""; ""; @do(
   comp1 := @Compare(Names1; Names2;
   [CASEINSENSITIVE] : [ACCENTINSENSITIVE]);
   comp2 := @Replace(@Text(comp1); "-1" : "0" : "1";
   " is less than " : " is equal to " : " is greater than ");
   Names1 + comp2 + Names2))

3. This computed field formula for a multi-value field named Column2 compares Column1 with A and Z to see if its values start in the alphabetic range. Text is posted to Column2 when the value in Column1 is out of range.

   @If(Column1 = ""; ""; @Do(
   Low1 := @Compare(Column1; "A"; [CASEINSENSITIVE]);
   High1 := @Compare(Column1; "Z"; [CASEINSENSITIVE]);
   Low2 := @Replace(@Text(Low1); "-1" : "0" : "1"; "Does not start with alpha" : "" : "");
   High2 := @Replace(@Text(High1); "-1" : "0" : "1"; "" : "" : "Does not start with alpha");
   Low2 + High2))

4. This formula retrieves all the elements that begin with a, b, or c from the text list in the sailboats field:

   @For(n:=1;n <= @Elements(sailboats);n := n+1;
   FIELD result := @If(n=1;@If(@Compare(sailboats[n];"d";[CASEINSENSITIVE])=-1;
   sailboats[n];"");@If(@Compare(sailboats[n];"d";[CASEINSENSITIVE])=-1;
   result:(sailboats[n]);result)));
   result

   If the sailboats field contains "Hunter":"C&C":"Pearson":"Contessa":"Bristol," this formula returns "C&C;Contessa;Bristol."