1. Calculation functions
  2. All Functions
  3. Logical Functions
  4. COMPARE

Compares two text-formatted expressions to return a numeric result. The function comparison works at three levels:

  • comparing individual characters in the string, using the quantity of characters;
  • comparing accents applied to the characters, using the quantity of accents;
  • comparing the case applied to a character, using the greater number of upper case characters.

The result is:

  • 0, if text strings are found to be the same.
  • a positive integer if the first text string is greater than the second.
  • a negative integer if the first text string is less than the second.

There are two optional parameters available, which allow you to refine the comparison:

  • Mode parameters to control the strength of the comparison
  • A locale parameter

Syntax

COMPARE(x, y,[mode],[locale]) 

where:

  • x: text expression, text-formatted line items, text constants, or general expressions like 'Forename & " " & Surname' (where Forename and Surname are text formatted line items).
  • y: text expression, text-formatted line items, text constants, or general expressions like 'Forename & " " & Surname' (where Forename and Surname are text-formatted line items).
  • Mode has keywords which impose a different matching strength: (optional)
    • COMPARE(x, y, PRIMARY): Compares on base character.
    • COMPARE(x, y, SECONDARY): Compares on base character and accent.
    • COMPARE(x, y, TERTIARY): Compares on base character, accent, and case.
    • COMPARE(x, y, IDENTICAL): Compares for absolute identity.
      • If no mode parameter is used, then comparison defaults to TERTIARY.
  • Locale parameter: to make the text comparison locale-sensitive. (optional)

Back to Top

Format

Input Format Output Format

x: Text

y: Text

o: Keyword

l: Country Code

Number

Back to Top

Arguments

The function uses the following arguments:

  • x: Text formatted line item, text constant, or general expression
  • y: Text formatted line item, text constant, or general expression
  • o: Keywords 'IDENTICAL', 'TERTIARY' (default), 'SECONDARY', or 'PRIMARY'
  • l: Locale - two letter country code - For example, COMPARE (x, y, TERTIARY, fr) makes the comparison sensitive for French locale variations

Back to Top

Constraints

The function has the following constraints:

  • Source line items must be text-formatted.
  • Result line item must be number-formatted.

Back to Top

Excel equivalent

Back to Top

Example

Default Comparison

In this example, COMPARE uses the text-formatted source line items as the two mandatory parameters. 0 is returned only when the base character, accent, and case in both line items match:

COMPARE(Top Monthly Sales North, Top Monthly Sales South)

PRIMARY Comparison

In this example, the function uses the PRIMARY mode parameter. If the comparison finds a difference in base character, a negative or non-negative integer is returned:

COMPARE(Top Monthly Sales North, Top Monthly Sales South, PRIMARY)

SECONDARY Comparison

In this example, the function uses the SECONDARY mode parameter. If the comparison finds a difference in base character or accent, a negative or non-negative integer is returned:

COMPARE(Top Monthly Sales North, Top Monthly Sales South, SECONDARY)

TERTIARY Comparison

In this example, the function uses the TERTIARY mode parameter. If the comparison finds a difference in base character or accent or case, a negative or non-negative integer is returned:

COMPARE(Top Monthly Sales North, Top Monthly Sales South, TERTIARY)

IDENTICAL Comparison

In this example, the function uses the IDENTICAL mode parameter. If the comparison finds any kind of difference, including one in base character or accent or case mismatch, a negative or non-negative integer is returned.

Note that the text strings in the Jul 12 column return 0 for a TERTIARY comparison for base character, accent, and case match. Contrast this with the IDENTICAL comparison—a positive integer is returned because the source text strings are not absolutely identical:

  • Top Monthly Sales North text contains a single A-grave Unicode character (00C0) as first character.
  • Top Monthly Sales South text contains a combined A-grave two Unicode character (0300 with 0041) as first character:
COMPARE(Top Monthly Sales North, Top Monthly Sales South, IDENTICAL)

Comparison with locale specified

In this example, the function uses the PRIMARY mode parameter and sv as the locale parameter to make the comparison sensitive to established variations for the Swedish language. For Swedish locale, v and w are treated as the same base character:

Note: When using a PRIMARY comparison for base character with no locale parameter the text strings in the Aug 12 column return a result of 1. When the PRIMARY comparison uses the sv locale parameter, a result of 0 is returned.

COMPARE(Top Monthly Sales North, Top Monthly Sales South, PRIMARY, sv)

Back to Top

Similar functions