MATCH( ) function

Returns a logical value indicating if a value matches any of the values it was compared against.

Syntax

MATCH(comparison_value, test <,...n>)

Parameters

comparison_value

Any expression or field.

test

Any expression or field you want to compare with comparison_value. You can specify as many test values as necessary, but all values specified must be of the same data type.

Output

Logical. Returns T (true) if at least one match is found, and F (false) otherwise.

Remarks

You can use this function to replace expressions that use the OR operator. For example, City=“PORTLAND” OR City=“SEATTLE” is equivalent to MATCH(City, “PORTLAND”, “SEATTLE”).

The MATCH( ) function is case-sensitive. If you are working with data that includes inconsistent letter casing, you can use the UPPER( ) or LOWER( ) function to convert values to consistent casing before using the MATCH( ) function.

The result of this function can be affected by the SET EXACT option. This option is turned off by default, and when it is off ACL compares character fields or expressions based on the length of the shortest character field or expression. For example, “Dept” and “Department” would match. If the SET EXACT option is turned on, “Dept” would match other instances of “Dept”, but would not match either “D” or “Department”. To ensure that the Exact Character Comparisons option is not used, even if it is selected in the ACL Options dialog box, you can add the SET EXACT OFF command to the top of your script.

Example

The following example opens the Customers table and filters out any records that do not have “SEATTLE” or “PORTLAND” specified in the CITY field. The UPPER( ) function is used to ensure that matches are not missed because the city names have been entered using inconsistent letter casing. The entries in the CITY field are converted to uppercase before being compared with the uppercase city names.

OPEN Customers
SET FILTER TO MATCH(UPPER(CITY), "PORTLAND", "SEATTLE")


(C) 2013 ACL Services Ltd. All Rights Reserved. | Send feedback