How to identify similar numbers from database before insert

Question

I have a table with 20 million rows of phone numbers, which is synced to azure cognitive search. I need to search before creating a new row. Lets say I have a record with number "1234567890" if a request comes with number "1234567899" or "1234557890", this request is slightly similar to the one which is already in db. Is there any way to do that in MSSQL or Cognitive search or in Azure or any other tool.

Thanks in advance

Answer 1

Thanks for reaching here! Could you share more details, on the structure of the data. And the use-case.

One thing you could possibly do is fuzzy search and define default distance as 1 or 2 to find out similar matches. But it can get tricky beyond a certain distance. Plus the whole operation would make indexing pretty slow, because you will check all the records before inserting.

Check this document on Fuzzy search - Azure Cognitive Search | Microsoft Learn

Doing the exact phone number might be easier, if you make that as the ID for the index. Otherwise Fuzzy search can help.

Let us know.