What do you do when you need to determine if two strings are almost the same? Check it out in the video:
Here’s the second version
PROCEDURE FuzzyCompare(s1: Text; s2: Text): Decimal;
VAR
hit: Integer;
p1: Integer;
p2: Integer;
l1: Integer;
l2: Integer;
pt: Integer;
diff: Integer;
hstr: Text;
test: ARRAY[2500] OF Boolean;
BEGIN
begin
if StrLen(s1) < StrLen(s2) then begin
hstr := s2;
s2 := s1;
s1 := hstr;
end;
l1 := StrLen(s1);
l2 := StrLen(s2);
p1 := 1;
p2 := 1;
if l1 > l2 then
diff := l2 div 3 + Abs(l1 - l2)
else
diff := l1 div 3 + Abs(l1 - l2);
repeat
if not test[p1] then begin
if (s1[p1] = s2[p2]) and (Abs(p1 - p2) <= diff) then begin
test[p1] := true;
hit := hit + 1;
p1 := p1 + 1;
p2 := p2 + 1;
if p1 > l1 then
p1 := 1;
end else begin
test[p1] := false;
p1 := p1 + 1;
if p1 > l1 then begin
while (p1 > 1) and not (test[p1]) do
p1 := p1 - 1;
p2 := p2 + 1
end;
end;
end else begin
p1 := p1 + 1;
if p1 > l1 then begin
repeat
p1 := p1 - 1;
until (p1 = 1) or test[p1];
p2 := p2 + 1;
end;
end;
until p2 > l2;
exit(hit / l1);
end;
END;
When working with real-world data in Business Central — importing customers, merging company databases, or cleaning up duplicates — you quickly discover that string comparison isn’t as simple as checking if two values are equal. Names get misspelled, punctuation varies, and formatting differs. In this video, Erik demonstrates two approaches to fuzzy string comparison in AL: the built-in Levenshtein distance function and a custom similarity algorithm that returns a percentage-based match score.
The Problem: Real-World Data is Messy
Imagine you need to import a list of customers into Business Central, but you only want to create new records — existing customers should be updated instead. The catch? The incoming data might not be spelled exactly the same as what’s already in your system. There could be a comma here, a dash there, or a minor typo. Our eyes can instantly recognize that “Hogard Software Incorporated” and “Hogard, Software Inc.” are the same company, but code needs a different approach.
This is where fuzzy string comparison comes in — instead of asking “are these strings identical?” we ask “how similar are these strings?”
Approach 1: Levenshtein Distance with Type Helper
The Levenshtein distance algorithm calculates the minimum number of single-character edits (insertions, deletions, or substitutions) required to transform one string into another. The good news is that you don’t need to implement this yourself — Microsoft has already included it in the Type Helper codeunit.
If you look inside the Type Helper codeunit and search for “Levenshtein,” you’ll find a function called TextDistance that does exactly this calculation. Using it is straightforward:
var
T: Codeunit "Type Helper";
A, B: Text;
Output: Integer;
begin
Output := T.TextDistance(A, B);
end;Here are some examples of how it works:
- “abc” → “” (empty string): Distance of 3 — three deletions, which makes sense.
- “abc” → “dec”: Distance of 2 — two character substitutions.
- “abc” → “cba”: Distance of 2 — the ‘b’ stays in the middle, but ‘a’ and ‘c’ swap.
- “hogard software incorporated” → “hogard-software incorporated”: Distance of 6.
The Levenshtein distance gives you an absolute number of edits. This is useful, but it has a limitation: a distance of 5 means something very different for a 10-character string versus a 100-character string. Five edits in a very long string is probably a close match, while five edits in a short string could mean they’re completely different.
Approach 2: A Custom Fuzzy Compare Function
The second approach uses a custom algorithm that returns a decimal value between 0 and 1, representing the similarity ratio. A value of 1 means a perfect match, and lower values indicate less similarity. This approach inherently accounts for string length, making it useful in different scenarios than raw edit distance.
Erik notes that this piece of code has been circulating in the Dynamics community for a long time — he first used it in a Danish project integrating Navision with a marketing database of all companies in Denmark, to prevent creating duplicate records. The exact origin and formal name of the algorithm are unclear, but it works by sliding through both strings, checking for character matches within a tolerance window, and counting hits.
Here’s the full implementation:
PROCEDURE FuzzyCompare(s1: Text; s2: Text): Decimal;
VAR
hit: Integer;
p1: Integer;
p2: Integer;
l1: Integer;
l2: Integer;
pt: Integer;
diff: Integer;
hstr: Text;
test: ARRAY[2500] OF Boolean;
BEGIN
begin
if StrLen(s1) < StrLen(s2) then begin
hstr := s2;
s2 := s1;
s1 := hstr;
end;
l1 := StrLen(s1);
l2 := StrLen(s2);
p1 := 1;
p2 := 1;
if l1 > l2 then
diff := l2 div 3 + Abs(l1 - l2)
else
diff := l1 div 3 + Abs(l1 - l2);
repeat
if not test[p1] then begin
if (s1[p1] = s2[p2]) and (Abs(p1 - p2) <= diff) then begin
test[p1] := true;
hit := hit + 1;
p1 := p1 + 1;
p2 := p2 + 1;
if p1 > l1 then
p1 := 1;
end else begin
test[p1] := false;
p1 := p1 + 1;
if p1 > l1 then begin
while (p1 > 1) and not (test[p1]) do
p1 := p1 - 1;
p2 := p2 + 1
end;
end;
end else begin
p1 := p1 + 1;
if p1 > l1 then begin
repeat
p1 := p1 - 1;
until (p1 = 1) or test[p1];
p2 := p2 + 1;
end;
end;
until p2 > l2;
exit(hit / l1);
end;
END;The algorithm works by:
- Ensuring
s1is always the longer string (swapping if necessary). - Calculating a tolerance window (
diff) based on the string lengths — roughly one-third of the shorter string’s length plus the length difference. - Walking through both strings simultaneously, counting character matches that fall within the tolerance window.
- Returning the ratio of hits to the length of the longer string.
The Complete Fuzzy Workbench Page
The full page ties both approaches together so you can compare them side by side:
page 58100 "Fuzzy"
{
PageType = Card;
layout
{
area(Content)
{
field(A; A)
{
ApplicationArea = all;
trigger OnValidate()
begin
Output := T.TextDistance(A, B);
Output2 := FuzzyCompare(A, B);
end;
}
field(B; B)
{
ApplicationArea = all;
trigger OnValidate()
begin
Output := T.TextDistance(A, B);
Output2 := FuzzyCompare(A, B);
end;
}
field(Output; Output)
{
ApplicationArea = all;
}
field(Output2; Output2)
{
ApplicationArea = all;
}
}
}
var
T: Codeunit "Type Helper";
A, B : Text;
Output: Integer;
Output2: Decimal;
}Comparing the Two Approaches
Here’s how the two methods compare with some example inputs:
- “hogard” vs “hogard”: Levenshtein = 0, Fuzzy = 1 (perfect match).
- “hogard” vs “hogård” (Danish characters): Levenshtein = 2, Fuzzy = 0.75.
- “hogard incorporated” vs “hogard incorporoted” (typo): Levenshtein = 2, Fuzzy = 0.92.
- “hogard, software inc.” vs “hogard software inc.”: Levenshtein = 1, Fuzzy = 0.93.
The key difference: Levenshtein gives you an absolute edit count, while the custom function gives you a relative similarity score. For long strings, a Levenshtein distance of 5 might actually indicate a very close match, but the raw number doesn’t tell you that. The fuzzy compare’s decimal output naturally scales with string length.
When to Use Fuzzy String Comparison
Fuzzy string comparison is a valuable tool for your toolbox in scenarios like:
- Data conversions — migrating data from one system to another while avoiding duplicates.
- Company mergers — importing a large set of new records into existing data and matching up what already exists.
- Duplicate detection — finding potential duplicate customers, vendors, or contacts that were entered with slight variations.
- Data quality checks — flagging records that are suspiciously similar for human review.
Summary
Business Central gives you a built-in Levenshtein distance calculation via Type Helper.TextDistance(), which is great for quick edit-distance checks. For scenarios where you need a normalized similarity score that accounts for string length, a custom fuzzy compare function like the one shown here can be more practical. Both tools help you move beyond simple exact-match comparisons and handle the messy reality of real-world data. The full source code for this example is available on GitHub for you to experiment with.
