{"id":7951,"date":"2026-11-17T12:54:16","date_gmt":"2026-11-17T20:54:16","guid":{"rendered":"https:\/\/devblogs.microsoft.com\/azure-sql\/?p=7951"},"modified":"2026-11-17T12:54:16","modified_gmt":"2026-11-17T20:54:16","slug":"fuzzy-string-matching-is-now-generally-available-across-microsoft-sql","status":"publish","type":"post","link":"https:\/\/devblogs.microsoft.com\/azure-sql\/fuzzy-string-matching-is-now-generally-available-across-microsoft-sql\/","title":{"rendered":"Fuzzy string matching is now generally available across Microsoft SQL"},"content":{"rendered":"<p class=\"code-line\" dir=\"auto\" data-line=\"2\"><em>Four native T-SQL functions that tell you how close two strings are, not just whether they&#8217;re equal.<\/em><\/p>\n<p class=\"code-line\" dir=\"auto\" data-line=\"4\"><code>MICROSFT<\/code>\u00a0isn&#8217;t\u00a0<code>MICROSOFT<\/code>.\u00a0<code>MARHTA<\/code>\u00a0isn&#8217;t\u00a0<code>MARTHA<\/code>.\u00a0<code>INV-1O08<\/code>\u00a0isn&#8217;t\u00a0<code>INV-1008<\/code>. To an equality check, each pair is simply different. To your business, each is a near miss worth catching.<\/p>\n<p class=\"code-line\" dir=\"auto\" data-line=\"6\"><strong>Today, fuzzy string matching is generally available in Microsoft SQL.<\/strong>\u00a0<code>EDIT_DISTANCE<\/code>\u00a0tells you\u00a0<code>MICROSFT<\/code>\u00a0is one edit from\u00a0<code>MICROSOFT<\/code>.\u00a0<code>JARO_WINKLER_SIMILARITY<\/code>\u00a0scores\u00a0<code>MARTHA<\/code>\u00a0and\u00a0<code>MARHTA<\/code>\u00a0at 96 out of 100. It all runs in T-SQL, right where your data lives, with no external libraries, models, or custom code.<\/p>\n<p class=\"code-line\" dir=\"auto\" data-line=\"8\">Available across all SKUs of:<\/p>\n<ul class=\"code-line\" dir=\"auto\" data-line=\"10\">\n<li class=\"code-line\" dir=\"auto\" data-line=\"10\"><strong>Azure SQL Database<\/strong><\/li>\n<li class=\"code-line\" dir=\"auto\" data-line=\"11\"><strong>Azure SQL Managed Instance<\/strong><\/li>\n<li class=\"code-line\" dir=\"auto\" data-line=\"12\"><strong>SQL database in Microsoft Fabric<\/strong><\/li>\n<li class=\"code-line\" dir=\"auto\" data-line=\"13\"><strong>SQL Server 2025, starting with Cumulative Update 11 (CU11), and later cumulative updates<\/strong><\/li>\n<\/ul>\n<h2 id=\"what-fuzzy-string-matching-actually-means\" class=\"code-line\" dir=\"auto\" data-line=\"15\">What fuzzy string matching actually means<\/h2>\n<p class=\"code-line\" dir=\"auto\" data-line=\"17\"><strong>Fuzzy string matching measures how closely two strings resemble each other without requiring an exact match.<\/strong><\/p>\n<p class=\"code-line\" dir=\"auto\" data-line=\"19\">Think of it as comparing the written form of two values. An exact comparison asks, &#8220;Are these equal under our comparison rules?&#8221; Fuzzy matching adds, &#8220;If not, how close are they?&#8221;<\/p>\n<p class=\"code-line\" dir=\"auto\" data-line=\"21\">For an edit-distance algorithm, closeness can be measured by the operations needed to transform one string into the other:<\/p>\n<pre><code class=\"code-line language-text\" dir=\"auto\" data-line=\"23\">FORM\r\n  swap O and R\r\nFROM\r\n<\/code><button class=\"code-block-copy-button\" aria-label=\"Copy code block\"><\/button><\/pre>\n<p class=\"code-line\" dir=\"auto\" data-line=\"29\">One adjacent swap makes the strings identical. Insertion, deletion, substitution, and transposition are\u00a0<strong>transformation operations<\/strong>, not assumptions about what originally caused the difference.<\/p>\n<p class=\"code-line\" dir=\"auto\" data-line=\"31\">This is useful for typo-tolerant lookup, candidate duplicate detection, master-data reconciliation, and reviewing text captured by optical character recognition (OCR).<\/p>\n<p class=\"code-line\" dir=\"auto\" data-line=\"33\">It is not the same as understanding meaning.\u00a0<code>car<\/code>\u00a0and\u00a0<code>automobile<\/code>\u00a0are related concepts but have very different character sequences. Nor does a high similarity score establish that two records represent the same person, product, or organization.<\/p>\n<p class=\"code-line\" dir=\"auto\" data-line=\"35\"><strong>Fuzzy matching provides evidence for a decision, not the decision itself.<\/strong><\/p>\n<h2 id=\"meet-the-four-functions\" class=\"code-line\" dir=\"auto\" data-line=\"37\">Meet the four functions<\/h2>\n<p class=\"code-line\" dir=\"auto\" data-line=\"39\">The feature provides two algorithm families, each exposed through a distance function and a similarity function.<\/p>\n<table class=\"code-line\" dir=\"auto\" data-line=\"41\">\n<thead class=\"code-line\" dir=\"auto\" data-line=\"41\">\n<tr class=\"code-line\" dir=\"auto\" data-line=\"41\">\n<th>Function<\/th>\n<th>Algorithm<\/th>\n<th>Return type<\/th>\n<th>How to read the result<\/th>\n<\/tr>\n<\/thead>\n<tbody class=\"code-line\" dir=\"auto\" data-line=\"43\">\n<tr class=\"code-line\" dir=\"auto\" data-line=\"43\">\n<td><code>EDIT_DISTANCE<\/code><\/td>\n<td>Damerau-Levenshtein (Optimal String Alignment)<\/td>\n<td><code>int<\/code><\/td>\n<td>Edit count, or an over-budget marker when limited; lower means closer<\/td>\n<\/tr>\n<tr class=\"code-line\" dir=\"auto\" data-line=\"44\">\n<td><code>EDIT_DISTANCE_SIMILARITY<\/code><\/td>\n<td>Damerau-Levenshtein (Optimal String Alignment)<\/td>\n<td><code>int<\/code><\/td>\n<td>Normalized score from 0 to 100; higher means closer<\/td>\n<\/tr>\n<tr class=\"code-line\" dir=\"auto\" data-line=\"45\">\n<td><code>JARO_WINKLER_DISTANCE<\/code><\/td>\n<td>Jaro-Winkler<\/td>\n<td><code>float<\/code><\/td>\n<td>Distance from 0 to 1; lower means closer<\/td>\n<\/tr>\n<tr class=\"code-line\" dir=\"auto\" data-line=\"46\">\n<td><code>JARO_WINKLER_SIMILARITY<\/code><\/td>\n<td>Jaro-Winkler<\/td>\n<td><code>int<\/code><\/td>\n<td>Score from 0 to 100; higher means closer<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<p class=\"code-line\" dir=\"auto\" data-line=\"48\">Their signatures are:<\/p>\n<pre><code class=\"code-line language-syntaxsql\" dir=\"auto\" data-line=\"50\">EDIT_DISTANCE (\r\n    character_expression\r\n    , character_expression [ , maximum_distance ]\r\n)\r\n\r\nEDIT_DISTANCE_SIMILARITY (\r\n    character_expression\r\n    , character_expression\r\n)\r\n\r\nJARO_WINKLER_DISTANCE (\r\n    character_expression\r\n    , character_expression\r\n)\r\n\r\nJARO_WINKLER_SIMILARITY (\r\n    character_expression\r\n    , character_expression\r\n)\r\n<\/code><button class=\"code-block-copy-button\" aria-label=\"Copy code block\"><\/button><\/pre>\n<p class=\"code-line\" dir=\"auto\" data-line=\"72\">The inputs can be constants, variables, or columns. Use bounded character types such as\u00a0<code>varchar(n)<\/code>\u00a0or\u00a0<code>nvarchar(n)<\/code>;\u00a0<code>varchar(max)<\/code>\u00a0and\u00a0<code>nvarchar(max)<\/code>\u00a0are not supported. If an input is\u00a0<code>NULL<\/code>, the result is\u00a0<code>NULL<\/code>.<\/p>\n<h3 id=\"a-first-comparison\" class=\"code-line\" dir=\"auto\" data-line=\"74\">A first comparison<\/h3>\n<p class=\"code-line\" dir=\"auto\" data-line=\"76\">Each example is self-contained, creates no permanent objects, and applies a supported collation (<code>Latin1_General_100_CI_AS_SC<\/code>) with\u00a0<code>COLLATE<\/code>, so you can run it without changing your database collation.<\/p>\n<pre><code class=\"code-line language-sql\" dir=\"auto\" data-line=\"78\">;<span class=\"hljs-keyword\">WITH<\/span> Pair <span class=\"hljs-keyword\">AS<\/span> (\r\n    <span class=\"hljs-keyword\">SELECT<\/span>\r\n        N<span class=\"hljs-string\">'Colour'<\/span> <span class=\"hljs-keyword\">COLLATE<\/span> Latin1_General_100_CI_AS_SC <span class=\"hljs-keyword\">AS<\/span> SourceText,\r\n        N<span class=\"hljs-string\">'Color'<\/span> <span class=\"hljs-keyword\">COLLATE<\/span> Latin1_General_100_CI_AS_SC <span class=\"hljs-keyword\">AS<\/span> TargetText\r\n)\r\n<span class=\"hljs-keyword\">SELECT<\/span> SourceText, TargetText,\r\n       EDIT_DISTANCE(SourceText, TargetText) <span class=\"hljs-keyword\">AS<\/span> Edits,\r\n       EDIT_DISTANCE_SIMILARITY(SourceText, TargetText) <span class=\"hljs-keyword\">AS<\/span> EditScore,\r\n       JARO_WINKLER_DISTANCE(SourceText, TargetText) <span class=\"hljs-keyword\">AS<\/span> JwDistance,\r\n       JARO_WINKLER_SIMILARITY(SourceText, TargetText) <span class=\"hljs-keyword\">AS<\/span> JwScore\r\n<span class=\"hljs-keyword\">FROM<\/span> Pair;\r\n<\/code><button class=\"code-block-copy-button\" aria-label=\"Copy code block\"><\/button><\/pre>\n<p class=\"code-line\" dir=\"auto\" data-line=\"92\">The query returns:<\/p>\n<table class=\"code-line\" dir=\"auto\" data-line=\"94\">\n<thead class=\"code-line\" dir=\"auto\" data-line=\"94\">\n<tr class=\"code-line\" dir=\"auto\" data-line=\"94\">\n<th>Edits<\/th>\n<th>EditScore<\/th>\n<th>JwDistance, approximately<\/th>\n<th>JwScore<\/th>\n<\/tr>\n<\/thead>\n<tbody class=\"code-line\" dir=\"auto\" data-line=\"96\">\n<tr class=\"code-line\" dir=\"auto\" data-line=\"96\">\n<td>1<\/td>\n<td>83<\/td>\n<td>0.033333<\/td>\n<td>97<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<p class=\"code-line\" dir=\"auto\" data-line=\"98\">One deletion removes the\u00a0<code>u<\/code>. Both algorithm families recognize a close match, but they express closeness differently. A Jaro-Winkler distance of approximately\u00a0<code>0.033333<\/code>\u00a0is\u00a0<strong>not<\/strong>\u00a0a fractional number of edits.<\/p>\n<h2 id=\"damerau-levenshtein-optimal-string-alignment-how-many-edits\" class=\"code-line\" dir=\"auto\" data-line=\"100\">Damerau-Levenshtein (Optimal String Alignment): how many edits?<\/h2>\n<p class=\"code-line\" dir=\"auto\" data-line=\"102\"><code>EDIT_DISTANCE<\/code>\u00a0and\u00a0<code>EDIT_DISTANCE_SIMILARITY<\/code>\u00a0implement\u00a0<strong>Damerau-Levenshtein (Optimal String Alignment, or OSA)<\/strong>, the restricted Damerau-Levenshtein variant.<\/p>\n<p class=\"code-line\" dir=\"auto\" data-line=\"104\">Each permitted operation has a cost of one:<\/p>\n<table class=\"code-line\" dir=\"auto\" data-line=\"106\">\n<thead class=\"code-line\" dir=\"auto\" data-line=\"106\">\n<tr class=\"code-line\" dir=\"auto\" data-line=\"106\">\n<th>Operation<\/th>\n<th>Example<\/th>\n<th>What changes<\/th>\n<\/tr>\n<\/thead>\n<tbody class=\"code-line\" dir=\"auto\" data-line=\"108\">\n<tr class=\"code-line\" dir=\"auto\" data-line=\"108\">\n<td>Insertion<\/td>\n<td><code>CT<\/code>\u00a0to\u00a0<code>CAT<\/code><\/td>\n<td>Insert\u00a0<code>A<\/code><\/td>\n<\/tr>\n<tr class=\"code-line\" dir=\"auto\" data-line=\"109\">\n<td>Deletion<\/td>\n<td><code>CART<\/code>\u00a0to\u00a0<code>CAT<\/code><\/td>\n<td>Delete\u00a0<code>R<\/code><\/td>\n<\/tr>\n<tr class=\"code-line\" dir=\"auto\" data-line=\"110\">\n<td>Substitution<\/td>\n<td><code>CAT<\/code>\u00a0to\u00a0<code>CUT<\/code><\/td>\n<td>Replace\u00a0<code>A<\/code>\u00a0with\u00a0<code>U<\/code><\/td>\n<\/tr>\n<tr class=\"code-line\" dir=\"auto\" data-line=\"111\">\n<td>Adjacent transposition<\/td>\n<td><code>FORM<\/code>\u00a0to\u00a0<code>FROM<\/code><\/td>\n<td>Swap\u00a0<code>O<\/code>\u00a0and\u00a0<code>R<\/code><\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<p class=\"code-line\" dir=\"auto\" data-line=\"113\">The distance is the minimum cost of a transformation allowed by this algorithm, so each pair above has a distance of\u00a0<strong>1<\/strong>.<\/p>\n<h3 id=\"why-optimal-string-alignment-matters\" class=\"code-line\" dir=\"auto\" data-line=\"115\">Why &#8220;Optimal String Alignment&#8221; matters<\/h3>\n<p class=\"code-line\" dir=\"auto\" data-line=\"117\">The qualification is important: this is\u00a0<strong>not unrestricted Damerau-Levenshtein<\/strong>. In the Optimal String Alignment variant, the same substring cannot be edited more than once along the transformation.<\/p>\n<p class=\"code-line\" dir=\"auto\" data-line=\"119\">For example, unrestricted Damerau-Levenshtein can transform\u00a0<code>CA<\/code>\u00a0into\u00a0<code>ABC<\/code>\u00a0in two operations:<\/p>\n<pre><code class=\"code-line language-text\" dir=\"auto\" data-line=\"121\">CA -&gt; AC -&gt; ABC\r\n      swap   insert B\r\n<\/code><button class=\"code-block-copy-button\" aria-label=\"Copy code block\"><\/button><\/pre>\n<p class=\"code-line\" dir=\"auto\" data-line=\"126\">That sequence re-edits the transposed region. The restricted Damerau-Levenshtein (Optimal String Alignment) algorithm does not allow that transformation path, and the distance is\u00a0<strong>3<\/strong>, not 2.<\/p>\n<pre><code class=\"code-line language-sql\" dir=\"auto\" data-line=\"128\"><span class=\"hljs-keyword\">SELECT<\/span> EDIT_DISTANCE(\r\n           N<span class=\"hljs-string\">'CA'<\/span> <span class=\"hljs-keyword\">COLLATE<\/span> Latin1_General_100_CI_AS_SC,\r\n           N<span class=\"hljs-string\">'ABC'<\/span> <span class=\"hljs-keyword\">COLLATE<\/span> Latin1_General_100_CI_AS_SC\r\n       ) <span class=\"hljs-keyword\">AS<\/span> Edits;\r\n<span class=\"hljs-comment\">-- Result: 3<\/span>\r\n<\/code><button class=\"code-block-copy-button\" aria-label=\"Copy code block\"><\/button><\/pre>\n<p class=\"code-line\" dir=\"auto\" data-line=\"136\">For everyday typos this rarely matters, but it does when you compare results with another library or migrate an existing matching implementation.<\/p>\n<h3 id=\"from-edit-count-to-similarity\" class=\"code-line\" dir=\"auto\" data-line=\"138\">From edit count to similarity<\/h3>\n<p class=\"code-line\" dir=\"auto\" data-line=\"140\">An absolute edit count does not tell the whole story. Removing one character from a four-character value changes a larger proportion of the string than removing one character from a six-character value.<\/p>\n<p class=\"code-line\" dir=\"auto\" data-line=\"142\"><code>EDIT_DISTANCE_SIMILARITY<\/code>\u00a0normalizes edit distance using the longer input:<\/p>\n<pre><code class=\"code-line language-text\" dir=\"auto\" data-line=\"144\">(1 - (edit_distance \/ greatest(len(string1), len(string2)))) * 100\r\n<\/code><button class=\"code-block-copy-button\" aria-label=\"Copy code block\"><\/button><\/pre>\n<p class=\"code-line\" dir=\"auto\" data-line=\"148\">For example:<\/p>\n<ul class=\"code-line\" dir=\"auto\" data-line=\"150\">\n<li class=\"code-line\" dir=\"auto\" data-line=\"150\"><code>CART<\/code>\u00a0and\u00a0<code>CAT<\/code>:\u00a0<code>(1 - 1\/4) * 100 = 75<\/code>.<\/li>\n<li class=\"code-line\" dir=\"auto\" data-line=\"151\"><code>Colour<\/code>\u00a0and\u00a0<code>Color<\/code>:\u00a0<code>(1 - 1\/6) * 100<\/code>\u00a0is approximately\u00a0<code>83.33<\/code>; the function returns the integer\u00a0<code>83<\/code>.<\/li>\n<\/ul>\n<p class=\"code-line\" dir=\"auto\" data-line=\"153\">A score of 83 measures resemblance; it is\u00a0<strong>not an 83% probability<\/strong>\u00a0that the values refer to the same thing.<\/p>\n<h2 id=\"jaro-winkler-a-different-way-to-rank-resemblance\" class=\"code-line\" dir=\"auto\" data-line=\"155\">Jaro-Winkler: a different way to rank resemblance<\/h2>\n<p class=\"code-line\" dir=\"auto\" data-line=\"157\">Sometimes the question is not &#8220;How many edits are allowed?&#8221; but &#8220;Which names are the most plausible matches?&#8221;<\/p>\n<p class=\"code-line\" dir=\"auto\" data-line=\"159\">Jaro-Winkler considers matching characters within a positional matching window: a character must occur close enough to its position in the other string to count as a match. It also considers the relative order of those matches, then gives additional weight to a shared prefix. It does\u00a0<strong>not<\/strong>\u00a0calculate the minimum number of operations needed to transform one string into another.<\/p>\n<p class=\"code-line\" dir=\"auto\" data-line=\"161\">Consider\u00a0<code>MARTHA<\/code>\u00a0and\u00a0<code>MARHTA<\/code>. They share the same characters, with\u00a0<code>T<\/code>\u00a0and\u00a0<code>H<\/code>\u00a0out of order, and both start with\u00a0<code>MAR<\/code>.<\/p>\n<pre><code class=\"code-line language-sql\" dir=\"auto\" data-line=\"163\">;<span class=\"hljs-keyword\">WITH<\/span> Names <span class=\"hljs-keyword\">AS<\/span> (\r\n    <span class=\"hljs-keyword\">SELECT<\/span> NameA <span class=\"hljs-keyword\">COLLATE<\/span> Latin1_General_100_CI_AS_SC <span class=\"hljs-keyword\">AS<\/span> NameA,\r\n           NameB <span class=\"hljs-keyword\">COLLATE<\/span> Latin1_General_100_CI_AS_SC <span class=\"hljs-keyword\">AS<\/span> NameB\r\n    <span class=\"hljs-keyword\">FROM<\/span> (<span class=\"hljs-keyword\">VALUES<\/span>\r\n        (N<span class=\"hljs-string\">'MARTHA'<\/span>, N<span class=\"hljs-string\">'MARHTA'<\/span>),\r\n        (N<span class=\"hljs-string\">'CONTOSO HEALTH'<\/span>, N<span class=\"hljs-string\">'CONTOSO RETAIL'<\/span>)\r\n    ) <span class=\"hljs-keyword\">AS<\/span> n(NameA, NameB)\r\n)\r\n<span class=\"hljs-keyword\">SELECT<\/span> NameA, NameB,\r\n       EDIT_DISTANCE(NameA, NameB) <span class=\"hljs-keyword\">AS<\/span> Edits,\r\n       JARO_WINKLER_DISTANCE(NameA, NameB) <span class=\"hljs-keyword\">AS<\/span> JwDistance,\r\n       JARO_WINKLER_SIMILARITY(NameA, NameB) <span class=\"hljs-keyword\">AS<\/span> JwScore\r\n<span class=\"hljs-keyword\">FROM<\/span> Names\r\n<span class=\"hljs-keyword\">ORDER<\/span> <span class=\"hljs-keyword\">BY<\/span> JwScore <span class=\"hljs-keyword\">DESC<\/span>, NameA, NameB;\r\n<\/code><button class=\"code-block-copy-button\" aria-label=\"Copy code block\"><\/button><\/pre>\n<table class=\"code-line\" dir=\"auto\" data-line=\"180\">\n<thead class=\"code-line\" dir=\"auto\" data-line=\"180\">\n<tr class=\"code-line\" dir=\"auto\" data-line=\"180\">\n<th>NameA<\/th>\n<th>NameB<\/th>\n<th>Edits<\/th>\n<th>JwDistance, approximately<\/th>\n<th>JwScore<\/th>\n<\/tr>\n<\/thead>\n<tbody class=\"code-line\" dir=\"auto\" data-line=\"182\">\n<tr class=\"code-line\" dir=\"auto\" data-line=\"182\">\n<td><code>MARTHA<\/code><\/td>\n<td><code>MARHTA<\/code><\/td>\n<td>1<\/td>\n<td>0.038889<\/td>\n<td>96<\/td>\n<\/tr>\n<tr class=\"code-line\" dir=\"auto\" data-line=\"183\">\n<td><code>CONTOSO HEALTH<\/code><\/td>\n<td><code>CONTOSO RETAIL<\/code><\/td>\n<td>5<\/td>\n<td>0.082143<\/td>\n<td>92<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<p class=\"code-line\" dir=\"auto\" data-line=\"185\">Jaro-Winkler is worth evaluating for person and organization names when shared beginnings are meaningful and usually entered correctly. But prefixes can mislead:\u00a0<code>CONTOSO HEALTH<\/code>\u00a0and\u00a0<code>CONTOSO RETAIL<\/code>\u00a0score\u00a0<strong>92<\/strong>\u00a0despite five edits and may be different organizations. The prefix length and scaling factor are fixed; the functions don&#8217;t expose parameters for them.<\/p>\n<p class=\"code-line\" dir=\"auto\" data-line=\"187\"><strong>Choose Damerau-Levenshtein (OSA) for an explicit edit budget. Evaluate Jaro-Winkler for ranking character-based resemblance when prefix preference fits your data.<\/strong><\/p>\n<h2 id=\"use-maximum_distance-when-you-only-need-a-threshold\" class=\"code-line\" dir=\"auto\" data-line=\"189\">Use\u00a0<code>maximum_distance<\/code>\u00a0when you only need a threshold<\/h2>\n<p class=\"code-line\" dir=\"auto\" data-line=\"191\">Suppose your application accepts candidates within two edits. An exact distance of eight is unnecessary if all you need to know is that the candidate exceeds the budget.<\/p>\n<p class=\"code-line\" dir=\"auto\" data-line=\"193\">The optional\u00a0<code>maximum_distance<\/code>\u00a0argument lets\u00a0<code>EDIT_DISTANCE<\/code>\u00a0stop early once the distance exceeds the threshold. Within the threshold, it returns the actual distance; beyond it, it returns\u00a0<strong><code>maximum_distance + 1<\/code><\/strong>.<\/p>\n<pre><code class=\"code-line language-sql\" dir=\"auto\" data-line=\"195\">;<span class=\"hljs-keyword\">WITH<\/span> Pairs <span class=\"hljs-keyword\">AS<\/span> (\r\n    <span class=\"hljs-keyword\">SELECT<\/span> SourceText <span class=\"hljs-keyword\">COLLATE<\/span> Latin1_General_100_CI_AS_SC <span class=\"hljs-keyword\">AS<\/span> SourceText,\r\n           TargetText <span class=\"hljs-keyword\">COLLATE<\/span> Latin1_General_100_CI_AS_SC <span class=\"hljs-keyword\">AS<\/span> TargetText\r\n    <span class=\"hljs-keyword\">FROM<\/span> (<span class=\"hljs-keyword\">VALUES<\/span>\r\n        (N<span class=\"hljs-string\">'CART'<\/span>, N<span class=\"hljs-string\">'CAT'<\/span>),\r\n        (N<span class=\"hljs-string\">'FORM'<\/span>, N<span class=\"hljs-string\">'FROM'<\/span>),\r\n        (N<span class=\"hljs-string\">'Chocolate'<\/span>, N<span class=\"hljs-string\">'Sweets'<\/span>),\r\n        (N<span class=\"hljs-string\">'CAT'<\/span>, N<span class=\"hljs-string\">'CAT'<\/span>)\r\n    ) <span class=\"hljs-keyword\">AS<\/span> p(SourceText, TargetText)\r\n)\r\n<span class=\"hljs-keyword\">SELECT<\/span> SourceText, TargetText,\r\n       EDIT_DISTANCE(SourceText, TargetText) <span class=\"hljs-keyword\">AS<\/span> ActualDistance,\r\n       EDIT_DISTANCE(SourceText, TargetText, <span class=\"hljs-number\">2<\/span>) <span class=\"hljs-keyword\">AS<\/span> BoundedDistance\r\n<span class=\"hljs-keyword\">FROM<\/span> Pairs\r\n<span class=\"hljs-keyword\">ORDER<\/span> <span class=\"hljs-keyword\">BY<\/span> SourceText;\r\n<\/code><button class=\"code-block-copy-button\" aria-label=\"Copy code block\"><\/button><\/pre>\n<table class=\"code-line\" dir=\"auto\" data-line=\"213\">\n<thead class=\"code-line\" dir=\"auto\" data-line=\"213\">\n<tr class=\"code-line\" dir=\"auto\" data-line=\"213\">\n<th>Pair<\/th>\n<th>ActualDistance<\/th>\n<th>BoundedDistance<\/th>\n<\/tr>\n<\/thead>\n<tbody class=\"code-line\" dir=\"auto\" data-line=\"215\">\n<tr class=\"code-line\" dir=\"auto\" data-line=\"215\">\n<td><code>CART<\/code>\u00a0\/\u00a0<code>CAT<\/code><\/td>\n<td>1<\/td>\n<td>1<\/td>\n<\/tr>\n<tr class=\"code-line\" dir=\"auto\" data-line=\"216\">\n<td><code>CAT<\/code>\u00a0\/\u00a0<code>CAT<\/code><\/td>\n<td>0<\/td>\n<td>0<\/td>\n<\/tr>\n<tr class=\"code-line\" dir=\"auto\" data-line=\"217\">\n<td><code>Chocolate<\/code>\u00a0\/\u00a0<code>Sweets<\/code><\/td>\n<td>8<\/td>\n<td>3<\/td>\n<\/tr>\n<tr class=\"code-line\" dir=\"auto\" data-line=\"218\">\n<td><code>FORM<\/code>\u00a0\/\u00a0<code>FROM<\/code><\/td>\n<td>1<\/td>\n<td>1<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<p class=\"code-line\" dir=\"auto\" data-line=\"220\">The value\u00a0<strong>3<\/strong>\u00a0for\u00a0<code>Chocolate<\/code>\u00a0\/\u00a0<code>Sweets<\/code>\u00a0means\u00a0<strong>&#8220;over the budget of two&#8221;<\/strong>, not &#8220;exactly three edits.&#8221; Use the bounded form to accept or reject candidates, not to rank how far apart rejected values are.<\/p>\n<h2 id=\"put-it-to-work-typo-tolerant-lookup\" class=\"code-line\" dir=\"auto\" data-line=\"222\">Put it to work: typo-tolerant lookup<\/h2>\n<p class=\"code-line\" dir=\"auto\" data-line=\"224\">Let&#8217;s return to\u00a0<code>MICROSFT<\/code>. We want a short list of catalog values no more than one edit away.<\/p>\n<pre><code class=\"code-line language-sql\" dir=\"auto\" data-line=\"226\"><span class=\"hljs-keyword\">DECLARE<\/span> <span class=\"hljs-variable\">@query<\/span> nvarchar(<span class=\"hljs-number\">40<\/span>) <span class=\"hljs-operator\">=<\/span> N<span class=\"hljs-string\">'MICROSFT'<\/span>;\r\n\r\n;<span class=\"hljs-keyword\">WITH<\/span> Catalog <span class=\"hljs-keyword\">AS<\/span> (\r\n    <span class=\"hljs-keyword\">SELECT<\/span> Name <span class=\"hljs-keyword\">COLLATE<\/span> Latin1_General_100_CI_AS_SC <span class=\"hljs-keyword\">AS<\/span> Name\r\n    <span class=\"hljs-keyword\">FROM<\/span> (<span class=\"hljs-keyword\">VALUES<\/span>\r\n        (N<span class=\"hljs-string\">'MICROSFT'<\/span>), (N<span class=\"hljs-string\">'MICROSOFT'<\/span>), (N<span class=\"hljs-string\">'CONTOSO'<\/span>)\r\n    ) <span class=\"hljs-keyword\">AS<\/span> c(Name)\r\n)\r\n<span class=\"hljs-keyword\">SELECT<\/span> Name,\r\n       EDIT_DISTANCE(\r\n           Name, <span class=\"hljs-variable\">@query<\/span> <span class=\"hljs-keyword\">COLLATE<\/span> Latin1_General_100_CI_AS_SC\r\n       ) <span class=\"hljs-keyword\">AS<\/span> Distance\r\n<span class=\"hljs-keyword\">FROM<\/span> Catalog\r\n<span class=\"hljs-keyword\">WHERE<\/span> EDIT_DISTANCE(\r\n          Name, <span class=\"hljs-variable\">@query<\/span> <span class=\"hljs-keyword\">COLLATE<\/span> Latin1_General_100_CI_AS_SC, <span class=\"hljs-number\">1<\/span>\r\n      ) <span class=\"hljs-operator\">&lt;=<\/span> <span class=\"hljs-number\">1<\/span>\r\n<span class=\"hljs-keyword\">ORDER<\/span> <span class=\"hljs-keyword\">BY<\/span> Distance, Name;\r\n<\/code><button class=\"code-block-copy-button\" aria-label=\"Copy code block\"><\/button><\/pre>\n<p class=\"code-line\" dir=\"auto\" data-line=\"246\">The result contains\u00a0<code>MICROSFT<\/code>\u00a0with distance 0, followed by\u00a0<code>MICROSOFT<\/code>\u00a0with distance 1;\u00a0<code>CONTOSO<\/code>\u00a0is outside the budget. The misspelled catalog entry is included deliberately: exact matches rank first, and the application can offer the alternatives instead of silently rewriting the input. The bounded call filters, while the unbounded call supplies the ranking value.<\/p>\n<h2 id=\"find-candidate-duplicates-without-comparing-everyone-to-everyone\" class=\"code-line\" dir=\"auto\" data-line=\"248\">Find candidate duplicates without comparing everyone to everyone<\/h2>\n<p class=\"code-line\" dir=\"auto\" data-line=\"250\">Customer deduplication needs both textual resemblance and business context. Comparing every customer with every other customer produces a number of pairs that grows approximately with the square of the row count.<\/p>\n<p class=\"code-line\" dir=\"auto\" data-line=\"252\">A practical first step is\u00a0<strong>blocking<\/strong>: group records using a trusted attribute, then compare names within those groups.<\/p>\n<pre><code class=\"code-line language-sql\" dir=\"auto\" data-line=\"254\">;<span class=\"hljs-keyword\">WITH<\/span> Customer <span class=\"hljs-keyword\">AS<\/span> (\r\n    <span class=\"hljs-keyword\">SELECT<\/span> CustomerId,\r\n           Name <span class=\"hljs-keyword\">COLLATE<\/span> Latin1_General_100_CI_AS_SC <span class=\"hljs-keyword\">AS<\/span> Name,\r\n           PostalCode\r\n    <span class=\"hljs-keyword\">FROM<\/span> (<span class=\"hljs-keyword\">VALUES<\/span>\r\n        (<span class=\"hljs-number\">1<\/span>, N<span class=\"hljs-string\">'MARTHA'<\/span>, N<span class=\"hljs-string\">'98052'<\/span>),\r\n        (<span class=\"hljs-number\">2<\/span>, N<span class=\"hljs-string\">'MARHTA'<\/span>, N<span class=\"hljs-string\">'98052'<\/span>),\r\n        (<span class=\"hljs-number\">3<\/span>, N<span class=\"hljs-string\">'MARTHA'<\/span>, N<span class=\"hljs-string\">'10001'<\/span>),\r\n        (<span class=\"hljs-number\">4<\/span>, N<span class=\"hljs-string\">'ROBERT'<\/span>, N<span class=\"hljs-string\">'98052'<\/span>)\r\n    ) <span class=\"hljs-keyword\">AS<\/span> c(CustomerId, Name, PostalCode)\r\n)\r\n<span class=\"hljs-keyword\">SELECT<\/span> a.CustomerId <span class=\"hljs-keyword\">AS<\/span> CustomerA, b.CustomerId <span class=\"hljs-keyword\">AS<\/span> CustomerB,\r\n       a.Name <span class=\"hljs-keyword\">AS<\/span> NameA, b.Name <span class=\"hljs-keyword\">AS<\/span> NameB,\r\n       JARO_WINKLER_SIMILARITY(a.Name, b.Name) <span class=\"hljs-keyword\">AS<\/span> Score\r\n<span class=\"hljs-keyword\">FROM<\/span> Customer <span class=\"hljs-keyword\">AS<\/span> a\r\n<span class=\"hljs-keyword\">JOIN<\/span> Customer <span class=\"hljs-keyword\">AS<\/span> b\r\n  <span class=\"hljs-keyword\">ON<\/span> a.PostalCode <span class=\"hljs-operator\">=<\/span> b.PostalCode\r\n <span class=\"hljs-keyword\">AND<\/span> a.CustomerId <span class=\"hljs-operator\">&lt;<\/span> b.CustomerId\r\n<span class=\"hljs-keyword\">WHERE<\/span> JARO_WINKLER_SIMILARITY(a.Name, b.Name) <span class=\"hljs-operator\">&gt;=<\/span> <span class=\"hljs-number\">92<\/span>\r\n<span class=\"hljs-keyword\">ORDER<\/span> <span class=\"hljs-keyword\">BY<\/span> Score <span class=\"hljs-keyword\">DESC<\/span>, CustomerA, CustomerB;\r\n<\/code><button class=\"code-block-copy-button\" aria-label=\"Copy code block\"><\/button><\/pre>\n<p class=\"code-line\" dir=\"auto\" data-line=\"277\">Three details make the query useful:<\/p>\n<ol class=\"code-line\" dir=\"auto\" data-line=\"279\">\n<li class=\"code-line\" dir=\"auto\" data-line=\"279\"><strong>Postal-code equality creates the shortlist.<\/strong>\u00a0Only customers within the same postal code are compared.<\/li>\n<li class=\"code-line\" dir=\"auto\" data-line=\"280\"><strong><code>a.CustomerId &lt; b.CustomerId<\/code>\u00a0emits each pair once.<\/strong>\u00a0It excludes self-comparisons and reversed duplicates such as\u00a0<code>(2,1)<\/code>\u00a0when\u00a0<code>(1,2)<\/code>\u00a0already exists.<\/li>\n<li class=\"code-line\" dir=\"auto\" data-line=\"281\"><strong>The similarity filter flags plausible names.<\/strong>\u00a0<code>MARTHA<\/code>\u00a0and\u00a0<code>MARHTA<\/code>\u00a0pass the illustrative cutoff; pairs involving\u00a0<code>ROBERT<\/code>\u00a0do not.<\/li>\n<\/ol>\n<p class=\"code-line\" dir=\"auto\" data-line=\"283\">The query returns one pair: customer 1 and customer 2, with a similarity score of\u00a0<strong>96<\/strong>.<\/p>\n<p class=\"code-line\" dir=\"auto\" data-line=\"285\">The other\u00a0<code>MARTHA<\/code>, in postal code\u00a0<code>10001<\/code>, is not compared with the\u00a0<code>98052<\/code>\u00a0group. That illustrates the trade-off: blocking reduces comparisons but can miss true duplicates when the blocking attribute differs or is wrong.<\/p>\n<p class=\"code-line\" dir=\"auto\" data-line=\"287\">The cutoff of 92 is only illustrative:\u00a0<code>CONTOSO HEALTH<\/code>\u00a0and\u00a0<code>CONTOSO RETAIL<\/code>\u00a0also scored 92 earlier. Confirm candidates with independent evidence, such as a verified email or phone number.<\/p>\n<h2 id=\"reconcile-ocr-mistakes-within-the-right-business-context\" class=\"code-line\" dir=\"auto\" data-line=\"289\">Reconcile OCR mistakes within the right business context<\/h2>\n<p class=\"code-line\" dir=\"auto\" data-line=\"291\">An invoice reader captures\u00a0<code>INV-1O08<\/code>, confusing the letter\u00a0<code>O<\/code>\u00a0with the digit\u00a0<code>0<\/code>. Comparing against every invoice would be risky. Start with the vendor, then allow one character edit:<\/p>\n<pre><code class=\"code-line language-sql\" dir=\"auto\" data-line=\"293\">;<span class=\"hljs-keyword\">WITH<\/span> Captured <span class=\"hljs-keyword\">AS<\/span> (\r\n    <span class=\"hljs-keyword\">SELECT<\/span> <span class=\"hljs-number\">10<\/span> <span class=\"hljs-keyword\">AS<\/span> VendorId,\r\n           N<span class=\"hljs-string\">'INV-1O08'<\/span> <span class=\"hljs-keyword\">COLLATE<\/span> Latin1_General_100_CI_AS_SC <span class=\"hljs-keyword\">AS<\/span> Invoice\r\n), ReferenceInvoice <span class=\"hljs-keyword\">AS<\/span> (\r\n    <span class=\"hljs-keyword\">SELECT<\/span> VendorId,\r\n           Invoice <span class=\"hljs-keyword\">COLLATE<\/span> Latin1_General_100_CI_AS_SC <span class=\"hljs-keyword\">AS<\/span> Invoice\r\n    <span class=\"hljs-keyword\">FROM<\/span> (<span class=\"hljs-keyword\">VALUES<\/span>\r\n        (<span class=\"hljs-number\">10<\/span>, N<span class=\"hljs-string\">'INV-1008'<\/span>),\r\n        (<span class=\"hljs-number\">10<\/span>, N<span class=\"hljs-string\">'INV-1208'<\/span>),\r\n        (<span class=\"hljs-number\">20<\/span>, N<span class=\"hljs-string\">'INV-1008'<\/span>)\r\n    ) <span class=\"hljs-keyword\">AS<\/span> r(VendorId, Invoice)\r\n)\r\n<span class=\"hljs-keyword\">SELECT<\/span> c.VendorId, c.Invoice <span class=\"hljs-keyword\">AS<\/span> CapturedInvoice,\r\n       r.Invoice <span class=\"hljs-keyword\">AS<\/span> CandidateInvoice,\r\n       EDIT_DISTANCE(c.Invoice, r.Invoice) <span class=\"hljs-keyword\">AS<\/span> Edits\r\n<span class=\"hljs-keyword\">FROM<\/span> Captured <span class=\"hljs-keyword\">AS<\/span> c\r\n<span class=\"hljs-keyword\">JOIN<\/span> ReferenceInvoice <span class=\"hljs-keyword\">AS<\/span> r <span class=\"hljs-keyword\">ON<\/span> c.VendorId <span class=\"hljs-operator\">=<\/span> r.VendorId\r\n<span class=\"hljs-keyword\">WHERE<\/span> EDIT_DISTANCE(c.Invoice, r.Invoice, <span class=\"hljs-number\">1<\/span>) <span class=\"hljs-operator\">&lt;=<\/span> <span class=\"hljs-number\">1<\/span>\r\n<span class=\"hljs-keyword\">ORDER<\/span> <span class=\"hljs-keyword\">BY<\/span> c.VendorId, r.Invoice;\r\n<\/code><button class=\"code-block-copy-button\" aria-label=\"Copy code block\"><\/button><\/pre>\n<p class=\"code-line\" dir=\"auto\" data-line=\"315\">The query returns\u00a0<strong>two candidates<\/strong>\u00a0for vendor 10:<\/p>\n<table class=\"code-line\" dir=\"auto\" data-line=\"317\">\n<thead class=\"code-line\" dir=\"auto\" data-line=\"317\">\n<tr class=\"code-line\" dir=\"auto\" data-line=\"317\">\n<th>Candidate invoice<\/th>\n<th>Transformation<\/th>\n<th>Edits<\/th>\n<\/tr>\n<\/thead>\n<tbody class=\"code-line\" dir=\"auto\" data-line=\"319\">\n<tr class=\"code-line\" dir=\"auto\" data-line=\"319\">\n<td><code>INV-1008<\/code><\/td>\n<td>Replace\u00a0<code>O<\/code>\u00a0with\u00a0<code>0<\/code><\/td>\n<td>1<\/td>\n<\/tr>\n<tr class=\"code-line\" dir=\"auto\" data-line=\"320\">\n<td><code>INV-1208<\/code><\/td>\n<td>Replace\u00a0<code>O<\/code>\u00a0with\u00a0<code>2<\/code><\/td>\n<td>1<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<p class=\"code-line\" dir=\"auto\" data-line=\"322\">Vendor 20&#8217;s invoice is excluded by the vendor condition. Within vendor 10, both substitutions cost one edit; the function doesn&#8217;t know that OCR often confuses\u00a0<code>O<\/code>\u00a0and\u00a0<code>0<\/code>. Attributes such as amount and date must decide which invoice was intended.<\/p>\n<h2 id=\"useful-with-ai-without-being-an-ai-model\" class=\"code-line\" dir=\"auto\" data-line=\"324\">Useful with AI, without being an AI model<\/h2>\n<p class=\"code-line\" dir=\"auto\" data-line=\"326\">These functions require no embeddings, model training, or inference endpoint. They also fit naturally into AI-assisted workflows.<\/p>\n<p class=\"code-line\" dir=\"auto\" data-line=\"328\">For example, a language model might extract\u00a0<code>CONTOS<\/code>\u00a0from a document. The application can use fuzzy matching to shortlist names in its organization directory. The same SQL works if the input came from a person or a batch import instead.<\/p>\n<pre><code class=\"code-line language-sql\" dir=\"auto\" data-line=\"330\"><span class=\"hljs-keyword\">DECLARE<\/span> <span class=\"hljs-variable\">@entity<\/span> nvarchar(<span class=\"hljs-number\">100<\/span>) <span class=\"hljs-operator\">=<\/span> N<span class=\"hljs-string\">'CONTOS'<\/span>;\r\n\r\n;<span class=\"hljs-keyword\">WITH<\/span> Organization <span class=\"hljs-keyword\">AS<\/span> (\r\n    <span class=\"hljs-keyword\">SELECT<\/span> OrganizationId,\r\n           Name <span class=\"hljs-keyword\">COLLATE<\/span> Latin1_General_100_CI_AS_SC <span class=\"hljs-keyword\">AS<\/span> Name,\r\n           CountryCode\r\n    <span class=\"hljs-keyword\">FROM<\/span> (<span class=\"hljs-keyword\">VALUES<\/span>\r\n        (<span class=\"hljs-number\">1<\/span>, N<span class=\"hljs-string\">'CONTOSO'<\/span>, N<span class=\"hljs-string\">'US'<\/span>),\r\n        (<span class=\"hljs-number\">2<\/span>, N<span class=\"hljs-string\">'CONTOSO HEALTH'<\/span>, N<span class=\"hljs-string\">'US'<\/span>),\r\n        (<span class=\"hljs-number\">3<\/span>, N<span class=\"hljs-string\">'CONTOSO RETAIL'<\/span>, N<span class=\"hljs-string\">'US'<\/span>),\r\n        (<span class=\"hljs-number\">4<\/span>, N<span class=\"hljs-string\">'CONTOSO'<\/span>, N<span class=\"hljs-string\">'GB'<\/span>)\r\n    ) <span class=\"hljs-keyword\">AS<\/span> o(OrganizationId, Name, CountryCode)\r\n)\r\n<span class=\"hljs-keyword\">SELECT<\/span> TOP (<span class=\"hljs-number\">5<\/span>) OrganizationId, Name,\r\n       JARO_WINKLER_SIMILARITY(\r\n           Name, <span class=\"hljs-variable\">@entity<\/span> <span class=\"hljs-keyword\">COLLATE<\/span> Latin1_General_100_CI_AS_SC\r\n       ) <span class=\"hljs-keyword\">AS<\/span> Score\r\n<span class=\"hljs-keyword\">FROM<\/span> Organization\r\n<span class=\"hljs-keyword\">WHERE<\/span> CountryCode <span class=\"hljs-operator\">=<\/span> N<span class=\"hljs-string\">'US'<\/span>\r\n  <span class=\"hljs-keyword\">AND<\/span> JARO_WINKLER_SIMILARITY(\r\n          Name, <span class=\"hljs-variable\">@entity<\/span> <span class=\"hljs-keyword\">COLLATE<\/span> Latin1_General_100_CI_AS_SC\r\n      ) <span class=\"hljs-operator\">&gt;=<\/span> <span class=\"hljs-number\">80<\/span>\r\n<span class=\"hljs-keyword\">ORDER<\/span> <span class=\"hljs-keyword\">BY<\/span> Score <span class=\"hljs-keyword\">DESC<\/span>, OrganizationId;\r\n<\/code><button class=\"code-block-copy-button\" aria-label=\"Copy code block\"><\/button><\/pre>\n<p class=\"code-line\" dir=\"auto\" data-line=\"356\">The query returns three US candidates:\u00a0<code>CONTOSO<\/code>\u00a0with a score of\u00a0<strong>97<\/strong>, followed by\u00a0<code>CONTOSO HEALTH<\/code>\u00a0and\u00a0<code>CONTOSO RETAIL<\/code>\u00a0at\u00a0<strong>89<\/strong>\u00a0each. The GB record is excluded. Change\u00a0<code>@entity<\/code>\u00a0to\u00a0<code>N'ZZZZ'<\/code>\u00a0and the query returns no rows; the application should treat that as &#8220;no match&#8221; rather than force a best guess.<\/p>\n<p class=\"code-line\" dir=\"auto\" data-line=\"358\">Fuzzy matching won&#8217;t expand an acronym into a company name or resolve related meanings. Use an alias table or a semantic approach for those tasks.<\/p>\n<h2 id=\"where-this-fits-in-the-sql-matching-toolbox\" class=\"code-line\" dir=\"auto\" data-line=\"360\">Where this fits in the SQL matching toolbox<\/h2>\n<p class=\"code-line\" dir=\"auto\" data-line=\"362\">Fuzzy string matching extends existing capabilities; it does not replace them.<\/p>\n<table class=\"code-line\" dir=\"auto\" data-line=\"364\">\n<thead class=\"code-line\" dir=\"auto\" data-line=\"364\">\n<tr class=\"code-line\" dir=\"auto\" data-line=\"364\">\n<th>Question<\/th>\n<th>Appropriate tool<\/th>\n<th>Example<\/th>\n<\/tr>\n<\/thead>\n<tbody class=\"code-line\" dir=\"auto\" data-line=\"366\">\n<tr class=\"code-line\" dir=\"auto\" data-line=\"366\">\n<td>Are these values equal under the intended comparison rules?<\/td>\n<td>Equality, joins, and an appropriate collation<\/td>\n<td>Match a trusted customer key<\/td>\n<\/tr>\n<tr class=\"code-line\" dir=\"auto\" data-line=\"367\">\n<td>Does the text contain a known pattern or substring?<\/td>\n<td><code>LIKE<\/code>,\u00a0<code>CHARINDEX<\/code>,\u00a0<code>PATINDEX<\/code><\/td>\n<td>Find a known prefix<\/td>\n<\/tr>\n<tr class=\"code-line\" dir=\"auto\" data-line=\"368\">\n<td>Does the text follow a structured pattern?<\/td>\n<td><code>REGEXP_*<\/code>\u00a0functions<\/td>\n<td>Validate or extract a formatted value<\/td>\n<\/tr>\n<tr class=\"code-line\" dir=\"auto\" data-line=\"369\">\n<td>Do these values have similar phonetic representations?<\/td>\n<td><code>SOUNDEX<\/code>,\u00a0<code>DIFFERENCE<\/code><\/td>\n<td>Coarse screening of English-oriented names<\/td>\n<\/tr>\n<tr class=\"code-line\" dir=\"auto\" data-line=\"370\">\n<td>How far apart are these values in character edits?<\/td>\n<td><code>EDIT_DISTANCE<\/code>,\u00a0<code>EDIT_DISTANCE_SIMILARITY<\/code><\/td>\n<td>Measure edit count or normalized edit similarity<\/td>\n<\/tr>\n<tr class=\"code-line\" dir=\"auto\" data-line=\"371\">\n<td>How similar are character alignment and prefixes?<\/td>\n<td><code>JARO_WINKLER_DISTANCE<\/code>,\u00a0<code>JARO_WINKLER_SIMILARITY<\/code><\/td>\n<td>Rank plausible name matches<\/td>\n<\/tr>\n<tr class=\"code-line\" dir=\"auto\" data-line=\"372\">\n<td>Which documents contain relevant words or phrases?<\/td>\n<td>Full-text search<\/td>\n<td>Linguistic document retrieval<\/td>\n<\/tr>\n<tr class=\"code-line\" dir=\"auto\" data-line=\"373\">\n<td>Which representations are close in embedding space?<\/td>\n<td>Vector similarity\/search<\/td>\n<td>Semantic retrieval for AI applications<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<p class=\"code-line\" dir=\"auto\" data-line=\"375\">For example,\u00a0<code>SOUNDEX('Robert')<\/code>\u00a0and\u00a0<code>SOUNDEX('Rupert')<\/code>\u00a0both return\u00a0<code>R163<\/code>: a phonetic signal, which is different from the character-similarity scores announced here.<\/p>\n<h2 id=\"good-to-know\" class=\"code-line\" dir=\"auto\" data-line=\"377\">Good to know<\/h2>\n<ul class=\"code-line\" dir=\"auto\" data-line=\"379\">\n<li class=\"code-line\" dir=\"auto\" data-line=\"379\"><strong>Collation:<\/strong>\u00a0The functions support Windows collations and binary (<code>BIN<\/code>,\u00a0<code>BIN2<\/code>) collations. Non-binary\u00a0<code>SQL_*<\/code>\u00a0collations, such as the common default\u00a0<code>SQL_Latin1_General_CP1_CI_AS<\/code>, are not supported. Apply\u00a0<code>COLLATE<\/code>\u00a0to the inputs, as the examples do; you don&#8217;t need to change the database collation.<\/li>\n<li class=\"code-line\" dir=\"auto\" data-line=\"380\"><strong>Scale:<\/strong>\u00a0Narrow the candidate set with trusted business keys, such as region, vendor, or category, before scoring.<\/li>\n<li class=\"code-line\" dir=\"auto\" data-line=\"381\"><strong>Scores:<\/strong>\u00a0Test thresholds on your own labeled examples. A score is evidence of a possible match, not proof.<\/li>\n<\/ul>\n<h2 id=\"get-started\" class=\"code-line\" dir=\"auto\" data-line=\"383\">Get started<\/h2>\n<ul class=\"code-line\" dir=\"auto\" data-line=\"385\">\n<li class=\"code-line\" dir=\"auto\" data-line=\"385\"><strong>Azure SQL Database<\/strong>\u00a0and\u00a0<strong>SQL database in Microsoft Fabric:<\/strong>\u00a0The functions are available now.<\/li>\n<li class=\"code-line\" dir=\"auto\" data-line=\"386\"><strong>Azure SQL Managed Instance:<\/strong>\u00a0Use the SQL Server 2025 or Always-up-to-date update policy.<\/li>\n<li class=\"code-line\" dir=\"auto\" data-line=\"387\"><strong>SQL Server 2025:<\/strong>\u00a0Install\u00a0<strong>CU11 or later<\/strong>. If you tried the functions in preview, you no longer need the\u00a0<code>PREVIEW_FEATURES<\/code>\u00a0database scoped configuration to use them.<\/li>\n<\/ul>\n<p class=\"code-line\" dir=\"auto\" data-line=\"389\">Pick a column you already struggle to match, try the examples above on your own data, and see what turns up. Whether you&#8217;re cleaning customer records, forgiving a typo in a search box, or grounding an AI-extracted name, fuzzy string matching is now a native part of T-SQL, ready wherever your data lives.<\/p>\n<p class=\"code-line\" dir=\"auto\" data-line=\"391\">We&#8217;d love to hear what you build with it.<\/p>\n<h3 id=\"learn-more\" class=\"code-line\" dir=\"auto\" data-line=\"393\">Learn more<\/h3>\n<ul class=\"code-line\" dir=\"auto\" data-line=\"395\">\n<li class=\"code-line\" dir=\"auto\" data-line=\"395\"><a href=\"https:\/\/learn.microsoft.com\/en-us\/sql\/relational-databases\/fuzzy-string-match\/overview?view=fabric-sqldb\" data-href=\"https:\/\/learn.microsoft.com\/en-us\/sql\/relational-databases\/fuzzy-string-match\/overview?view=fabric-sqldb\">Fuzzy string matching overview<\/a><\/li>\n<li class=\"code-line\" dir=\"auto\" data-line=\"396\"><a href=\"https:\/\/learn.microsoft.com\/en-us\/sql\/t-sql\/functions\/edit-distance-transact-sql?view=sql-server-ver17\" data-href=\"https:\/\/learn.microsoft.com\/en-us\/sql\/t-sql\/functions\/edit-distance-transact-sql?view=sql-server-ver17\">EDIT_DISTANCE reference<\/a><\/li>\n<li class=\"code-line\" dir=\"auto\" data-line=\"397\"><a href=\"https:\/\/learn.microsoft.com\/en-us\/sql\/t-sql\/functions\/edit-distance-similarity-transact-sql?view=sql-server-ver17\" data-href=\"https:\/\/learn.microsoft.com\/en-us\/sql\/t-sql\/functions\/edit-distance-similarity-transact-sql?view=sql-server-ver17\">EDIT_DISTANCE_SIMILARITY reference<\/a><\/li>\n<li class=\"code-line\" dir=\"auto\" data-line=\"398\"><a href=\"https:\/\/learn.microsoft.com\/en-us\/sql\/t-sql\/functions\/jaro-winkler-distance-transact-sql?view=sql-server-ver17\" data-href=\"https:\/\/learn.microsoft.com\/en-us\/sql\/t-sql\/functions\/jaro-winkler-distance-transact-sql?view=sql-server-ver17\">JARO_WINKLER_DISTANCE reference<\/a><\/li>\n<li class=\"code-line\" dir=\"auto\" data-line=\"399\"><a href=\"https:\/\/learn.microsoft.com\/en-us\/sql\/t-sql\/functions\/jaro-winkler-similarity-transact-sql?view=sql-server-ver17\" data-href=\"https:\/\/learn.microsoft.com\/en-us\/sql\/t-sql\/functions\/jaro-winkler-similarity-transact-sql?view=sql-server-ver17\">JARO_WINKLER_SIMILARITY reference<\/a><\/li>\n<\/ul>\n","protected":false},"excerpt":{"rendered":"<p>Four native T-SQL functions that tell you how close two strings are, not just whether they&#8217;re equal. MICROSFT\u00a0isn&#8217;t\u00a0MICROSOFT.\u00a0MARHTA\u00a0isn&#8217;t\u00a0MARTHA.\u00a0INV-1O08\u00a0isn&#8217;t\u00a0INV-1008. To an equality check, each pair is simply different. To your business, each is a near miss worth catching. Today, fuzzy string matching is generally available in Microsoft SQL.\u00a0EDIT_DISTANCE\u00a0tells you\u00a0MICROSFT\u00a0is one edit from\u00a0MICROSOFT.\u00a0JARO_WINKLER_SIMILARITY\u00a0scores\u00a0MARTHA\u00a0and\u00a0MARHTA\u00a0at 96 out of 100. [&hellip;]<\/p>\n","protected":false},"author":29335,"featured_media":81,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[1,656,572,668,672,619],"tags":[650],"class_list":["post-7951","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-azure-sql","category-fabric-sql-database","category-managed-instance","category-sql-database-in-fabric","category-sql-server-2025","category-t-sql","tag-fuzzy-string-matching"],"acf":[],"blog_post_summary":"<p>Four native T-SQL functions that tell you how close two strings are, not just whether they&#8217;re equal. MICROSFT\u00a0isn&#8217;t\u00a0MICROSOFT.\u00a0MARHTA\u00a0isn&#8217;t\u00a0MARTHA.\u00a0INV-1O08\u00a0isn&#8217;t\u00a0INV-1008. To an equality check, each pair is simply different. To your business, each is a near miss worth catching. Today, fuzzy string matching is generally available in Microsoft SQL.\u00a0EDIT_DISTANCE\u00a0tells you\u00a0MICROSFT\u00a0is one edit from\u00a0MICROSOFT.\u00a0JARO_WINKLER_SIMILARITY\u00a0scores\u00a0MARTHA\u00a0and\u00a0MARHTA\u00a0at 96 out of 100. [&hellip;]<\/p>\n","_links":{"self":[{"href":"https:\/\/devblogs.microsoft.com\/azure-sql\/wp-json\/wp\/v2\/posts\/7951","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/devblogs.microsoft.com\/azure-sql\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/devblogs.microsoft.com\/azure-sql\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/azure-sql\/wp-json\/wp\/v2\/users\/29335"}],"replies":[{"embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/azure-sql\/wp-json\/wp\/v2\/comments?post=7951"}],"version-history":[{"count":1,"href":"https:\/\/devblogs.microsoft.com\/azure-sql\/wp-json\/wp\/v2\/posts\/7951\/revisions"}],"predecessor-version":[{"id":7991,"href":"https:\/\/devblogs.microsoft.com\/azure-sql\/wp-json\/wp\/v2\/posts\/7951\/revisions\/7991"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/azure-sql\/wp-json\/wp\/v2\/media\/81"}],"wp:attachment":[{"href":"https:\/\/devblogs.microsoft.com\/azure-sql\/wp-json\/wp\/v2\/media?parent=7951"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/azure-sql\/wp-json\/wp\/v2\/categories?post=7951"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/azure-sql\/wp-json\/wp\/v2\/tags?post=7951"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}