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 ( ).
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.