MATCH( ) function

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

Syntax

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

Parameters

comparison_value

Numeric, Character, or Datetime. The field, expression, or literal value to test for matches.

test

Numeric, Character, or Datetime. Any field, expression, or literal value you want to compare with comparison_value. You can specify as many test values as necessary, but all specified values must be of the same data type. You must enclose character literal values in quotation marks, and datetime values in backquotes.

Output

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

Remarks

You can use the MATCH( ) function to replace expressions that use the OR operator. For example, City="Phoenix" OR City="Austin" is equivalent to MATCH(City, "Phoenix", "Austin").

The comparison_value and test parameters can be numeric, character, or datetime fields, expressions, or literal values, but both parameters, including all test values, must belong to the same data category.

Numeric parameters

When numeric parameters being compared have a different decimal precision, the comparison uses the higher level of precision. For example, 1.23 does not match 1.234. Trailing zeros are ignored, so 1.23 does match 1.2300.

Character parameters

The MATCH( ) function is case-sensitive when used with character data. When it compares characters, “a” is not equivalent to “A”. If you are working with data that includes inconsistent case, you can use the UPPER( ) or LOWER( ) functions to convert the values to consistent case before using MATCH( ).

Partial matching is supported for character comparisons – that is, either value being compared can be contained by the other value and be considered a match. For example, MATCH("AB", "ABC") and MATCH("ABC", "AB") are both True. The shorter value must appear at the start of the longer value to constitute a match. Partial matching is enabled when SET EXACT = OFF. If SET EXACT = ON, partial matching is disabled and the comparison values must exactly match to constitute a match. For more information about SET EXACT (the Exact Character Comparisons option), see SET command.

If you want to ensure that the Exact Character Comparisons option is not used with the MATCH( ) function, check that the option is deselected in the Table tab in the Options dialog box (Tools > Options). If you are using a script, you can add the SET EXACT OFF command before the MATCH( ) function appears. If required, you can restore the previous state with the SET EXACT ON command.

Datetime parameters

You are not prevented from mixing date, datetime, and time values in the MATCH( ) function’s parameters, but mixing these Datetime subtypes can give results that are not meaningful. ACL uses serial number equivalents to process datetime calculations, so even if you are interested in only the date portion of a datetime value, the time portion still forms part of the calculation. For example:

MATCH(`20141231`,`20141229`,`20141231`) = True

MATCH(`20141231 120000`,`20141229`,`20141231`) = False

If we look at the serial number equivalent of these two expressions, we can see why the second one evaluates to false:

MATCH(42003.000000, 42001.000000, 42003.000000) = True

MATCH(42003.500000, 42001.000000, 42003.000000) = False

The serial number 42003.500000 is not equal to 42003.000000 even though the two dates are identical.

To avoid this problem, you can use other functions to harmonize Datetime subtypes. For example, this expression, which uses the same initial values as the second example above, evaluates to T (true):

MATCH(CTOD(DATE(`20141231 120000`,"YYYYMMDD"),"YYYYMMDD"),`20141229`,
`20141231`)

A date, datetime, or time field specified for any of the parameters can use any date, datetime, or time format, as long as the field definition correctly defines the format.

When specifying a literal date, datetime, or time value for any of the parameters, you are restricted to the formats in the table below, and you must enclose the value in backquotes – for example, `20141231`. For datetime values, you can use any combination of date, separator, and time formats. The date must precede the time, and you must use a separator between the two. Times must be specified using the 24-hour clock. Offsets from Coordinated Universal Time (UTC) must be prefaced by a plus sign (+) or a minus sign (-). Do not use any separators such as slashes (/) or colons (:) between the individual components of dates or times.

Date formats

Separator formats

Time formats

Examples

YYYYMMDD

single blank space

hhmmss

`20141231`

YYMMDD

the letter ‘t’

hhmm

`20141231 235959`

the letter ‘T’

hh

`141231 2359`

 

+/-hhmm

(UTC offset)

`141231t23`

+/-hh

(UTC offset)

`141231t235959-0500`

(Note: Do not use hh alone in the main time format with data that has a UTC offset. For example, avoid: hh+hhmm. Results can be unreliable.)

`141231T2359+01`

Examples

Note

Return values for character comparisons assume that SET EXACT is OFF (the default setting), except where noted.

Example Return value

MATCH("ABC", "BCD", "CDE", "AB")

T

MATCH(98, 99, 100, 101)

F

MATCH(Vendor_City, "Phoenix", "Austin", "Los Angeles")

Returns ‘T’ for all records that contain “Phoenix”, “Austin”, or “Los Angeles” in the Vendor_City field. Returns ‘F’ otherwise.

NOT MATCH(Vendor_City, "Phoenix", "Austin", "Los Angeles")

Returns ‘T’ for all records that do not contain “Phoenix”, “Austin”, or “Los Angeles” in the Vendor_City field. Returns ‘F’ otherwise.

MATCH("Phoenix", Vendor_City, City, City_2)

Returns ‘T’ for all records that contain “Phoenix” in the Vendor_City, City, or City_2 fields. Returns ‘F’ otherwise.

MATCH(UPPER(Vendor_City), "PHOENIX", "AUSTIN", "LOS ANGELES")

Returns ‘T’ for all records that contain “PHOENIX”, “AUSTIN”, or “LOS ANGELES” in the Vendor_City field, regardless of the case of any of the characters in the field. Returns ‘F’ otherwise.

The entries in the Vendor_City field are converted to uppercase before being compared with the uppercase city names.

MATCH(Product_Code, "A", "D", "F")

Returns ‘T’ for all records that have product codes “A”, “D”, or “F”, or product codes beginning with “A”, “D”, or “F”, in the Product_Code field. Returns ‘F’ otherwise.

MATCH(Product_Code, "A", "D", "F")

Returns ‘T’ for all records that have one-character product codes “A”, “D”, or “F” in the Product_Code field. Returns ‘F’ otherwise.

SET EXACT must be ON.

MATCH(Vendor_Address, Employee_Address)

Returns ‘T’ for all records that contain identical vendor and employee addresses. Returns ‘F’ otherwise.

You may need to use additional functions to standardize the format of vendor and employee addresses.

MATCH(Invoice_Date, `20140930`, `20141030`)

Returns ‘T’ for all records with an invoice date of 30 Sep 2014 or 30 Oct 2014. Returns ‘F’ otherwise.

NOT MATCH(Inventory_Value_at_Cost, Cost_x_Quantity)

Returns ‘T’ for all records in an Inventory table that contain different amounts in the Inventory_Value_at_Cost field and the computed Cost_x_Quantity field. Returns ‘F’ otherwise.

Extracting records for departments 101, 103, and 107

You can extract only records associated with departments 101, 103, or 107 using the following IF statement and the MATCH( ) function:

EXTRACT RECORD IF MATCH(Dept, "101", "103", "107") TO "Three_Departments"
Related reference
FIND( ) function
UPPER( ) function
LOWER( ) function


(C) 2015 ACL Services Ltd. All Rights Reserved.