Question
Given an algorithm of strings matching which works with certain strings (e.g. "123456789") and string patterns (e.g. "1*******9"). String patterns are not any kind of regexp or SQL LIKE pattern - they only provide "*" placeholder which means "a single digit or letter".
So, the algorithm will treat these values as "equal":
12ABCDE89
12A***E89
**A****8*
*********
The data is stored in the relational database (MS SQL Server) and .net core app addressed it via EntityFramework Core. The required scenario is to obtain 500 input strings (either certain or a pattern) and to find matched rows in the database (in the table containing 1 million of rows).
First I implemented it using LIKE pattern matching (first I transformed input strings to the LIKE pattern and then built a predicate for WHERE clause), but tests showed that it has unacceptable performance.
Can I implement this task using FULL-TEXT SEARCH feature of MSSQL? What will the predicate look like in this case? Any other ideas on the implementation?
Popular Answer
You can try CLR user-defined function approach (example). But you don't need to use SQL queries at all. Just compare 2 strings using your algorithm. Such approach theoretically should be faster
[SqlFunction(
DataAccess = DataAccessKind.None,
SystemDataAccess = SystemDataAccessKind.None,
IsPrecise = true,
IsDeterministic = true)
]
// Have to be public and static
public static bool CustomIsEqualTo(string baseString, string stringToCompare)
{
return true;
}
Related Questions
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow