MATCH( ) function

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

Syntax

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

Parameters

Name Type Description
comparison_value

character

numeric

datetime

 The field, expression, or literal value to test for matches.
test <,...n>

character

numeric

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:

MATCH(comparison_value, `20140930`, `20141030`)

Note

Inputs to the MATCH( ) function can be character, numeric, or datetime data. You cannot mix data types. All inputs must belong to the same data category.

Output

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

Examples

Basic examples

Note

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

Testing literal values

Returns T:

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

Returns F:

MATCH(98, 99, 100, 101)

Testing a field

Returns T for all records that contain "Phoenix", "Austin", or "Los Angeles" in the Vendor_City field. Returns F otherwise:

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:

NOT MATCH(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:

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

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

Testing multiple fields

Returns T for all records that contain "Phoenix" in the Vendor_City, City, or City_2 fields. Returns F otherwise:

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

SET EXACT behavior

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(Product_Code, "A", "D", "F")

Comparing two fields

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(Vendor_Address, Employee_Address)

Comparing dates

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

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

Advanced examples

Extracting anomalous inventory records

Use an IF statement and the MATCH( ) function to extract records that contain different amounts in the Inventory_Value_at_Cost field and the computed Cost_x_Quantity field:

EXTRACT RECORD IF NOT MATCH(Inventory_Value_at_Cost, Cost_x_Quantity) TO "Non_matching_amounts"

Extracting records for departments 101, 103, and 107

Use an IF statement and the MATCH( ) function to extract only records associated with departments 101, 103, or 107:

EXTRACT RECORD IF MATCH(Dept, "101", "103", "107") TO "Three_Departments"

Remarks

Use MATCH( ) instead of the OR operator

You can use the MATCH( ) function instead of expressions that use the OR operator.

For example:

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

is equivalent to

City="Phoenix" OR City="Austin" OR City="Los Angeles"

Decimal precision of numeric inputs

When the numeric inputs being compared have a different decimal precision, the comparison uses the higher level of precision.

Returns T, because 1.23 is equal to 1.23:

MATCH(1.23, 1.23, 1.25)

Returns F, because 1.23 does not equal 1.234 once the third decimal place is considered:

MATCH(1.23, 1.234, 1.25)

Character parameters

Case sensitivity

The MATCH( ) function is case-sensitive when used with character data. When it compares characters, "a" is not equivalent to "A".

Returns F:

MATCH("a","A","B","C")

If you are working with data that includes inconsistent case, you can use the UPPER( ) function to convert the values to consistent case before using MATCH( ).

Returns T:

MATCH(UPPER("a"), UPPER("A"), UPPER("B"),  UPPER("C"))

Partial matching

Partial matching is supported for character comparisons. Either value being compared can be contained by the other value and be considered a match.

Both of these examples return T:

MATCH("AB", "ABC")
MATCH("ABC", "AB")

Note

The shorter value must appear at the start of the longer value to constitute a match.

Partial matching and SET EXACT

Partial matching is enabled when SET EXACT = OFF, which is the Analytics default setting. If SET EXACT = ON, partial matching is disabled and the comparison values must exactly match to constitute a match.

Both examples above are False when SET EXACT is ON.

For more information about SET EXACT (the Exact Character Comparisons option), see SET command.

Turning SET EXACT off or on

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

A date, datetime, or time field specified as a function input can use any date, datetime, or time format, as long as the field definition correctly defines the format.

Mixing date, datetime, and time inputs

You are not prevented from mixing date, datetime, and time values in the MATCH( ) function's inputs, but mixing these Datetime subtypes can give results that are not meaningful.

Analytics 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.

Consider the following examples:

Returns T, because 31 December 2014 matches the second test value:

MATCH(`20141231`,`20141229`,`20141231`)

Returns F, even though the comparison_value and the second test value have an identical date of 31 December 2014:

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

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

Returns T, because the serial number comparison_value is equal to the second serial number test:

MATCH(42003.000000, 42001.000000, 42003.000000)

Returns F, because the serial number comparison_value does not equal any of the test values:

MATCH(42003.500000, 42001.000000, 42003.000000)

The date portion of the serial numbers 42003.500000 and 42003.000000 match, but the time portions do not. 0.500000 is the serial number equivalent of 12:00 PM.

Harmonize Datetime subtypes

To avoid the problems that can be caused by mixing Datetime subtypes, you can use functions to harmonize the subtypes.

For example, this expression, which uses the same initial values as the second example above, returns T rather than F:

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

Specifying a literal date, datetime, or time value

When specifying a literal date, datetime, or time value for any of the function inputs, you are restricted to the formats in the table below, and you must enclose the value in backquotes – for example, `20141231`.

Do not use any separators such as slashes (/) or colons (:) between the individual components of dates or times.

  • Datetime values – you can use any combination of the date, separator, and time formats listed in the table below. The date must precede the time, and you must use a separator between the two. Valid separators are a single blank space, the letter 't', or the letter 'T'.

  • Time values – you must specify times using the 24-hour clock. Offsets from Coordinated Universal Time (UTC) must be prefaced by a plus sign (+) or a minus sign (-).

    Example formats

    Example literal values

    YYYYMMDD

    `20141231`

    YYMMDD

    `141231`

    YYYYMMDD hhmmss

    `20141231 235959`

    YYMMDDthhmm

    `141231t2359`

    YYYYMMDDThh

    `20141231T23`

    YYYYMMDD hhmmss+/-hhmm

    (UTC offset)

    `20141231 235959-0500`

    YYMMDD hhmm+/-hh

    (UTC offset)

    `141231 2359+01`

    thhmmss

    `t235959`

    Thhmm

    `T2359`

    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.