Description
Returns space-separated matching keys for an organization name. Two names that are worth comparing share at least one key, so you can join two sources on their keys and score only those candidate pairs with FUZZY_MATCH and the company method, instead of comparing every row to every row with a cross join.
The keys are:
- the name’s distinctive words, without legal suffixes (Inc, LLC, Ltd, GmbH), entity-type words (Capital, Fund, Holdings) or very common words (Investment, National)
- its acronyms, both stated (
(ADIA), APG - All Pensions Group) and derived from its words. A parenthetical tag that doesn’t spell the name’s initials, such as (UK), isn’t a key
- the whole normalized name, prefixed with
n:, so identical names always share a key
Keys are lower case and sorted, so the same name always produces the same keys.
Syntax
NAME_MATCH_KEYS(string_expression)
Examples
NAME_MATCH_KEYS('ADIA') returns 'adia n:adia'.
NAME_MATCH_KEYS('Abu Dhabi Investment Authority (ADIA)') returns 'abu adi adia dhabi dia n:abu_dhabi_investment_authority'. It shares the key adia with 'ADIA'.
NAME_MATCH_KEYS('Temasek Holdings Pte Ltd') returns 'hpl n:temasek_holdings_pte_ltd temasek thp thpl'. It shares the key temasek with 'Temasek'.
NAME_MATCH_KEYS('H&M') returns 'hm n:hm'. Letters that spell a name are kept together, so 'H and M' and 'HM' share the key hm.
To produce one row per key, ready to join on, use it with TOKENIZE and FLATTEN in a Select component:
Return value datatype
String.
Notes
- A name with no usable words, such as
'!!!', an empty string or a placeholder like 'N/A', 'Unknown' or 'TBD', returns null, so blank names don’t all share a key and multiply in a join. To keep those rows on the side whose unmatched rows you want in the output, use COALESCE(NAME_MATCH_KEYS(name), '-'). - is never a real key, so the row joins nothing and comes through a left join unmatched.
- In a mixed-case value, upper case is used to recognize acronyms and US state codes (
NJ becomes new jersey), so pass the original value rather than LOWER(). In an all-caps value such as ACME CO, two-letter words are kept as words.
- Keys are meant for joining, not for display. A shared key only means the pair is worth scoring; use
FUZZY_MATCH(name1, name2, 'company') to decide whether it’s a match.
Impact of null value
Returns null if the input is null, has no usable words, or is a placeholder such as 'N/A'.Last modified on November 17, 2026