In SQL, the “in” operator is commonly used to test if a value exists in a list of values. For example:

SELECT * FROM TABLE WHERE CODE IN (‘a’, ‘b’, ‘f’);

New QV developers often spend some time looking for QV’s equivalent of the “in” operator. It’s the match() function.

LOAD * WHERE match(CODE, ‘a’, ‘b’, ‘f’);

The match() function, and its siblings “mixmatch” and “wildmatch”. are documented in the “Conditional Functions” section of the Ref guide and the help:

match( s, expr1 [ , expr2, …exprN ] )
Compares the string s to a list of strings or string expressions. The result of the comparison is an integer indicating which of the comparison strings/expressions matched. If no match is found, 0 is returned. The match function performs a case sensitive comparison.

mixmatch() works just like match() except it does a case insensitive comparision.

wildmatch() is another form that can be particularly useful. wildmatch() allows (but does not require) the “?’ and “*” wildcard characters in the match arguments.

wildMatch(text, ‘*error*’)

will match:

“An error has occurred”
“Error processing account nnnn”

QV 8.5 provides a “like” operator that allows for testing against a single value with wildcards:
text like ‘*error*’

Wildmatch can test against multiple values:

wildMatch(text, ‘*error*’, ‘*warning*’)

The match() functions return a number indicating which of the comparison strings was found. You can use this index number nested in a pick function to do “wildcard mapping” as an alternative to a nested if() function.

pick(
wildmatch(PartNo,
‘*99’, ‘P1586’, ‘?15*’, ‘?17*’, ‘*’
),
‘Taxes’, ‘Premium Fuel’, ‘Fuel’, ‘Lubricant’, ‘Other’)

-Rob