Match

Match allows you to test whether or not a string is the same as a text value you provide. This can be a specific value, or a a more complex match using Regular Expressions (regex) - a powerful and commonly used form of syntax for searching for patterns of characters e.g. in search engines.

With most tools, match returns a True|False, but in Excel match returns the index within a range of cells of a cell that meets your criteria. This can be combined with an INDEX function to reference values in multiple tables, see General > Lookups/Joins.

Excel

=~MATCH!~(‹target_string›, ‹within_array›«, match_type»)

Match Type Options:

• "0": MATCH finds the first value that is exactly equal to ‹target_string› and returns its index. The values in the ‹within_array› argument can be in any order.

• "1" or omitted: MATCH finds the largest value that is less than or equal to ‹target_string› and returns its index. The values in the ‹within_array› argument must be placed in ascending order, for example: -1, 0, 1, 2, 3, A-Z, FALSE, TRUE

• "-1": MATCH finds the smallest value that is greater than or equal to ‹target_string› and returns its index. The values in the ‹within_array› argument must be placed in descending order, for example: TRUE, FALSE, Z-A, 3, 2, 1, 0, -1

=MATCH(2.5,A1:A5,1) // Within [-1,0,1,2,3] returns 4

=MATCH(2.5,A1:A5,-1) // Within [3,2,1,0,-1] returns 1

=MATCH(2.5,A1:A5,0) // Within either array returns #N/A

Tableau

Tableau has no Match function so use an IF() to test for exact matches.

IF [Job Title] = "PA" THEN "Show" ELSE "Hide" END

Alteryx

~REGEX_MATCH!~(‹within_string›, "‹target_string›") where the result is only true when the ‹target_string› exactly matches the ‹within_string›.

REGEX_MATCH(UPPERCASE([Job Title]), "PA")

NB. The ‹target_string› can also be defined using Regular Expressions, or using the RegEx tool (in the Parse palette): or the Fuzzy Match tool (in the Join palette):

which allows you to test for approximate matches based on user-defined or standard-algorithm parameters.

OrgVue

In OrgVue, match() can be used to find specified strings and also in conjunction with Regular Expressions. Example 1 - String matching:

node.role.value.toUpperCase().match("MANAGE")
// Returns the word "MANAGE" if it appears in the Role value

Example 2 - Sophisticated matching of characters:

node.costcentre.value.match(/[0-9]/)
// Matches any numbers within the value for Cost Centre

Example 3 - Pattern matching:

node.ninumber.value.match(/[A-Za-z]{2}\s[0-9]{2}\s[0-9]{2}\s[0-9]{2}\s[A-Za-z]/g)
// Matches for a specific format of NI number, in this case,
// two letters-six numbers (grouped in pairs)-one letter, all separated with spaces
// e.g.: QQ 12 34 56 C

NB. For more information about the match() syntax, see the summary of Regular Expressions (Regex) here or test them out yourself at regexr.com.

tSQL

Example 1 - String matching: ‹within_field› ~LIKE!~ "‹target_string›"

SELECT *
FROM EmployeesData
WHERE JobTitle LIKE "PA"

Example 2: Instead of matching a specified strings, you can also use pattern matching, similar to Regular Expressions - a powerful and commonly used form of syntax for searching for patterns of characters e.g. in search engines. tSGL supports the following 'wildcard characters' in a pattern:

• % - match any string of 0+ characters

• _ - match any single character

• [] - match any single character within the range specified in brackets

• [^] match any single character not within the range specified in brackets

  For more examples, go to: https://msdn.microsoft.com/en-us/library/ms179859.aspx.

Python

In Python, matching can be done on specified strings and also in conjunction with Regular Expressions. Example 1 - String matching: ‹target_string› ~in!~ ‹within_string›

"PA" in jobTitle

Example 2 - Sophisticated matching of characters: ~re.search!~(‹pattern›, ‹field›)

import re

matcher = re.search('[A-Za-z]{2}\s[0-9]{2}\s[0-9]{2}\s[0-9]{2}\s[A-Za-z]', niNumber)

# matcher is None if no match, otherwise matcher is a Match object where matcher.group(0) is the matching string

NB.:

• This requires the Regular Expressions library to be imported (import re).

• The above examples assume variables have been declared for jobTitle and niNumber. For more information, go to General > Variables.