Member Avatar for MarlaFC

Hello All,

I came across with the following case and I would like to share it with you to get your opinions about.

In vb.net I use data from tables (oracle db) and i want to check two columns. Both columns have name of customers. All I want is to check if there are similar names. For example name "philip cole" could be the same with "p cole" or "cole philip" or "cole p". I'm trying to find an algorithmic logic to search between these two columns and find possible duplicates.

More specifid, i want to search if 4 or more consecutive characters appear in the other column.

Thank you for your time
MarlaFC

  • 2 Contributors
  • 2 Replies
  • 115 Views
  • 1 Day Discussion Span
  • Latest Post Latest Post by MarlaFC

Dani AI

Generated

Short answer: normalize both names, generate fixed-length character n‑grams (4 is fine), and test for intersection; use token checks or an edit-distance/phono filter to reduce false positives.

raised the right concerns about name order and initials; ’s SQL hint is workable but a .NET-side solution gives finer control for fuzzy comparison. Typical pitfalls are punctuation, diacritics, company suffixes (Inc, Ltd), and very common short fragments that match unrelated names. Normalize first (lowercase, strip diacritics and punctuation, optionally remove stopwords), then apply one or more of the tests below.

Practical algorithm and a compact VB.NET implementation for the 4-character rule:

  • Normalize names to a simple letter+digit string.
  • Build all length-4 substrings (4-grams) from one name into a HashSet.
  • Scan the other name’s 4-grams for any match — a single hit meets the OP requirement.
  • Optionally require multiple matching n-grams, or combine with token overlap and a Levenshtein/Jaro-Winkler check for stronger confidence (see Levenshtein distance).
Imports System.Text.RegularExpressions
Imports System.Text

Private Function NormalizeName(ByVal s As String) As String
    If String.IsNullOrWhiteSpace(s) Then Return String.Empty
    Dim t = s.ToLowerInvariant()
    Dim normalized = t.Normalize(System.Text.NormalizationForm.FormD)
    Dim sb As New StringBuilder()
    For Each ch As Char In normalized
        Dim cat = Globalization.CharUnicodeInfo.GetUnicodeCategory(ch)
        If cat <> Globalization.UnicodeCategory.NonSpacingMark Then sb.Append(ch)
    Next
    Return Regex.Replace(sb.ToString().Normalize(System.Text.NormalizationForm.FormC), "[^a-z0-9]", "")
End Function

Private Function HasCommonNGram(a As String, b As String, n As Integer) As Boolean
    a = NormalizeName(a) : b = NormalizeName(b)
    If a.Length < n OrElse b.Length < n Then Return False
    Dim grams As New HashSet(Of String)()
    For i As Integer = 0 To a.Length - n : grams.Add(a.Substring(i, n)) : Next
    For i As Integer = 0 To b.Length - n : If grams.Contains(b.Substring(i, n)) Then Return True
    Next
    Return False
End Function

Performance notes: precompute and store normalized signatures or n-grams for large tables, block comparisons by initial letter or phonetic code to avoid O(n^2) joins, and tune the minimum-match criteria to balance precision vs recall.

Recommended Answers

All 2 Replies

Member Avatar for Jx_Man

Use "Like" on your query statement.

Member Avatar for MarlaFC

Thank you Jx_Man for the sql help but since i have a loop statement to check every consecutive 4 letters, i would prefer a .net logic. I think i'll try to pass the columns into arrays and search inside them.

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.