SQL Server or C# - group similar records into one

Question

We have an SQL server where we log errors. I want to extract a sort of "error report" from it to see how many of each error there are for a given originator.

I am able to accomplish this by selecting from the appropriate table and grouping by originator and error and adding "Count(*)". I am using LINQ EF Core but can translate into an SQL query, if needed, to solve my below problem.
The problem is that there are errors like "Document 2361343 failed.", "Document 47464425 failed", etc.

How can I group all similar errors into one, like for example, "Document * failed"?

I'm thinking I could try with a Regex but I don't know how to incorporate it into the LINQ and/or SQL statement.

Ideally a general solution, perhaps an algorithm that would be able to detect similar messages and group them into one, would be ideal, but I'd settle just for solving the particular "Document * failed" problem, too.

IDEALLY I'd love for someone to tell me of an algorithm that can recognize similar text and group them. Basically this regex Document .* failed but smart enough to be able to work with any text with no hardcoding. So it would be able to recognize lala 5 yoyo and lala 4 yoyo without me having to tell it lala .* yoyo.

EDIT:

Example data in the table (skipping irrelevant columns):

Originator                      Error
-------------------------------------------------
Voltuas                         Record unavailable.
BonBon                          Order 47579 already shipped.
Voltuas                         Record unavailable
BizWalk                         Document 3527267 failed.
BizWalk                         Record unavailable.
Voltuas                         Order 7487 already shipped.
Gonda                           Document 78959 failed.
BizWalk                         Document 56739028 failed.

Desired output:

Voltuas - Record unavailable - 2
Voltuas - Order * already shipped. - 1
BonBon  - Order * already shipped. - 1
BizWalk - Document * failed - 2
BizWalk - Record unavailable - 1
Gonda   - Document * failed - 1

What I have so far (using EF Core latest):

context.Table_name.GroupBy(g => new { g.Originator, g.Error })
       .Select(x => new { Originator = x.Key.Originator, Error = x.Key.Error, Count = x.Count()}).ToList();

user_581076 · Answer

One option could be to GROUP BY the string with all numbers removed. For how to remove numbers from a string in SQL you can check out Remove numbers from string sql server.

This obviously won't work if you have variable strings inside error messages, but maybe you don't.

For entity framework Function Mappings of the Microsoft SQL Server Provider shows that Replace is supported.

The following is linq to objects, but it should work with the database too.

var l = new[] {
    new {Originator = "Voltuas", Error = "Record unavailable."},
    new {Originator = "BonBon", Error = "Order 47579 already shipped."},
    new {Originator = "Voltuas", Error = "Record unavailable"},
    new {Originator = "BizWalk", Error = "Document 3527267 failed."},
    new {Originator = "BizWalk", Error = "Record unavailable."},
    new {Originator = "Voltuas", Error = "Order 7487 already shipped."},
    new {Originator = "Gonda", Error = "Document 78959 failed."},
    new {Originator = "BizWalk", Error = "Document 56739028 failed."},
};


var grouped = l.Select( r => new { r.Originator, Error = r.Error.Replace("0","").Replace("1","").Replace("2","").Replace("3","").Replace("4","").Replace("5","").Replace("6","").Replace("7","").Replace("8","").Replace("9","")})
    .GroupBy(g => new { g.Originator, g.Error })
    .Select(x => new { Originator = x.Key.Originator, Error = x.Key.Error, Count = x.Count()})
    .ToList();

foreach (var g in grouped)
{
    Console.WriteLine($"{g.Originator}	'{g.Error}' = {g.Count} ");
}

This prints:

Voltuas 'Record unavailable.' = 1 
BonBon  'Order  already shipped.' = 1 
Voltuas 'Record unavailable' = 1 
BizWalk 'Document  failed.' = 2 
BizWalk 'Record unavailable.' = 1 
Voltuas 'Order  already shipped.' = 1 
Gonda   'Document  failed.' = 1

Please note that

Voltuas 'Record unavailable.' = 1 
Voltuas 'Record unavailable' = 1

are not grouped because one has a period and the other one doesn't. You can replace it too or more likely in your real data all of them will end with a period.

SQL Server or C# - group similar records into one

1 Answers