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 forAcme 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
levenshteinscore is the edit distance normalized by the longer string’s length, computed asround(100 * (1 - distance / maxLen)). soundexis 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 returnnull.- 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 (ADIAandAbu 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)andDepartment of Education (DOE)score53. A stated acronym has to spell the initials of the name’s words (counting words like “of”, soDepartment of Defense (DOD)works) or be the whole name. A tag such as(UK)is ignored, and- EMEAcounts as an ordinary word. Use a lookup join for nickname acronyms that aren’t initials. An acronym matched against a name’s initials (KKRandKohlberg 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 MandHMare the same name, as areAT&TandAT and T, andJ.P. MorganandJP Morgan. A name that is a single letter, such asX, is kept too. A lone letter inside a longer name is treated as an initial and ignored, soMacArthur FoundationmatchesJohn D. and Catherine T. MacArthur Foundation. - With
company, placeholder values such asN/A,Unknown,None,TBDand empty or punctuation-only values have no usable name, so they score0against any other value. Two identical values still score100, as with every method. - With
company, a parenthetical that isn’t an acronym is treated as a note about another organization and ignored, soNorthwind College (Contoso Advisors)is compared asNorthwind 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
Returnsnull if either string1 or string2 is null.