Skip to main content

Description

Returns an integer similarity score from 0 to 100 between two string values, where 100 is the strongest match and 0 is no similarity. Use it to filter rows by approximate match, deduplicate records with inconsistent spelling, or join on fields that do not match exactly. The function supports four comparison methods. Pick the one that fits the data: Matching is case-sensitive for levenshtein and jaro_winkler. Wrap the inputs in LOWER() for case-insensitive comparison. soundex is inherently case-insensitive. company compares case-insensitively, but in mixed-case values it uses upper case to recognize acronyms and state codes, so pass the original values rather than LOWER(). In an all-caps value such as ACME CO, two-letter words are kept as words rather than read as state codes.

Syntax

FUZZY_MATCH(string1, string2[, method])

Arguments

Examples

Keep rows whose company name is a close match for Acme Inc:
FUZZY_MATCH('Apple', 'Aple') returns 80. FUZZY_MATCH('Apple', 'Apple') returns 100. FUZZY_MATCH('Robert', 'Rupert', 'jaro_winkler') returns 85. FUZZY_MATCH('Smith', 'Smyth', 'soundex') returns 100. FUZZY_MATCH('Smith', 'Jones', 'soundex') returns 0. FUZZY_MATCH(LOWER(name1), LOWER(name2)) performs a case-insensitive Levenshtein comparison. FUZZY_MATCH('ADIA', 'Abu Dhabi Investment Authority (ADIA)', 'company') returns 100. FUZZY_MATCH('Temasek', 'Temasek Holdings Pte Ltd', 'company') returns 97. FUZZY_MATCH('Bain Capital', 'Bain & Company', 'company') returns 73, because each name has a word the other lacks.

Return value datatype

Integer (0–100).

Notes

  • The levenshtein score is the edit distance normalized by the longer string’s length, computed as round(100 * (1 - distance / maxLen)).
  • soundex is designed for English and Latin-script letters. Accented characters such as é are normalized to their base form before encoding, so 'José' matches 'Jose'. Inputs in non-Latin scripts (such as Cyrillic or CJK) cannot be phonetically encoded and return null.
  • Identical inputs always score 100 for every method.
  • With company, a name that is only an acronym scores 100 against a name that states the same acronym (ADIA and Abu Dhabi Investment Authority (ADIA)). When both names also have words, a shared acronym counts as one more matching word rather than a full match, because different organizations can use the same acronym: Department of Energy (DOE) and Department of Education (DOE) score 53. A stated acronym has to spell the initials of the name’s words (counting words like “of”, so Department of Defense (DOD) works) or be the whole name. A tag such as (UK) is ignored, and - EMEA counts as an ordinary word. Use a lookup join for nickname acronyms that aren’t initials. An acronym matched against a name’s initials (KKR and Kohlberg Kravis Roberts) scores at most 90, so a real word-for-word match wins over a coincidence of initials.
  • With company, letters that spell a name are kept together: H&M, H and M and HM are the same name, as are AT&T and AT and T, and J.P. Morgan and JP Morgan. A name that is a single letter, such as X, is kept too. A lone letter inside a longer name is treated as an initial and ignored, so MacArthur Foundation matches John D. and Catherine T. MacArthur Foundation.
  • With company, placeholder values such as N/A, Unknown, None, TBD and empty or punctuation-only values have no usable name, so they score 0 against any other value. Two identical values still score 100, as with every method.
  • With company, a parenthetical that isn’t an acronym is treated as a note about another organization and ignored, so Northwind College (Contoso Advisors) is compared as Northwind College. One typo is tolerated in words of six or more letters.
  • To match names across two sources without comparing every row to every row, generate candidate pairs with NAME_MATCH_KEYS first. See Matching company names across two sources.
  • The function raises an error if fewer than two arguments are supplied or if an unknown method is requested.

Impact of null value

Returns null if either string1 or string2 is null.
Last modified on November 17, 2026