A Comprehensive Guide to Implementing SQL LIKE Pattern Matching in C#: From Regular Expressions to Custom Algorithms

Introduction

In database queries, the SQL LIKE operator is a common tool for string pattern matching, using wildcards % (matches zero or more characters) and _ (matches one character) to define patterns. However, in C# applications, developers often need to perform similar pattern matching in memory without relying on database queries. This article explores various methods to implement SQL LIKE functionality in C#, with a focus on regex-based solutions and an analysis of alternative approaches.

Regular Expressions: The Core Tool for LIKE Functionality

Regular expressions are a powerful tool for string pattern matching, capable of covering all SQL LIKE features and even providing more complex matching capabilities. Although regex syntax differs from LIKE, we can map LIKE patterns to regex patterns through conversion. For example, the LIKE pattern abc_ef% *usd can be converted to the regex pattern \Aabc.ef.* \*usd\z, where \A and \z match the start and end of the string, respectively, . corresponds to _, and .* corresponds to %.

To achieve this conversion, we need to handle special characters in regex, such as ., $, ^, etc., which have special meanings in regex but may appear as literal characters in LIKE patterns. The following code demonstrates how to safely convert a LIKE pattern to a regex pattern:

public static class MyStringExtensions
{
    public static bool Like(this string toSearch, string toFind)
    {
        string pattern = "\\A" + 
                         new Regex(@"\.|\$|\^|\{|\[|\(|\||\)|\*|\+|\?|\\")
                             .Replace(toFind, ch => "\\" + ch)
                             .Replace('_', '.')
                             .Replace("%", ".*") + 
                         "\\z";
        return new Regex(pattern, RegexOptions.Singleline).IsMatch(toSearch);
    }
}

In this extension method, we first use a regex to escape special characters in the LIKE pattern, then replace _ with . and % with .*, and finally add \A and \z to ensure full-string matching. This allows us to call the method similarly to SQL LIKE:

bool result1 = "abcdefg".Like("abcd_fg"); // returns true
bool result2 = "abcdefg".Like("ab%f%");   // returns true
bool result3 = "abcdefghi".Like("abcd_fg"); // returns false

Alternative Approaches: String Methods for Simple Scenarios

For simple pattern matching needs, C# built-in string methods offer a lightweight solution. For example, Contains, StartsWith, and EndsWith methods correspond to LIKE patterns %value%, value%, and %value, respectively. The following code illustrates their usage:

string value = "samplevalue";
bool contains = value.Contains("eva");      // similar to '%eva%'
bool starts = value.StartsWith("sample");   // similar to 'sample%'
bool ends = value.EndsWith("value");       // similar to '%value'

When working with collections of strings, LINQ queries can be combined to achieve similar functionality:

List<string> values = new List<string> { "samplevalue1", "samplevalue2", "samplevalue3" };
var matches = values.Where(v => v.Contains("pattern")); // similar to '%pattern%'

This approach is suitable for scenarios with simple patterns and high-performance requirements, but it cannot handle complex wildcard combinations, such as _ or mixed use of % and _.

Advanced Implementation: Custom SQL LIKE Algorithm

For scenarios requiring full compatibility with Transact-SQL LIKE behavior, a custom algorithm may be necessary. This algorithm manually parses the pattern string, handling wildcards %, _, and character sets (e.g., [A-D] or [^A-D]) to achieve precise pattern matching. Below is a simplified framework of such an algorithm:

public static bool SqlLike(string pattern, string str)
{
    int patternIndex = 0;
    int lastWildCard = -1;
    bool isWildCardOn = false;

    for (int i = 0; i < str.Length; i++)
    {
        char c = str[i];
        if (patternIndex >= pattern.Length)
        {
            // Handle logic after pattern ends
            break;
        }
        char p = pattern[patternIndex];

        if (p == '%')
        {
            lastWildCard = patternIndex;
            isWildCardOn = true;
            // Skip consecutive '%' characters
            while (patternIndex < pattern.Length && pattern[patternIndex] == '%')
            {
                patternIndex++;
            }
        }
        else if (p == '_')
        {
            // Match one character
            patternIndex++;
        }
        else if (char.ToUpper(c) == char.ToUpper(p))
        {
            patternIndex++;
        }
        else if (isWildCardOn)
        {
            // Continue matching in wildcard mode
        }
        else
        {
            return false;
        }
    }
    // Check if the pattern is fully matched
    return patternIndex >= pattern.Length || pattern.Substring(patternIndex).All(ch => ch == '%');
}

The advantage of this algorithm is precise control over matching logic and support for complex patterns, but it is more complex to implement and may have lower performance compared to regex-based solutions.

Performance and Applicability Analysis

When choosing an appropriate solution, factors such as performance, complexity, and compatibility must be considered. The regex-based solution offers a good balance: it supports complex pattern matching, has concise code, and performs well in modern C#. For most application scenarios, the regex-based solution is recommended.

The string method solution performs best for simple pattern matching, as it avoids regex overhead, but its functionality is limited. The custom algorithm solution is necessary when full compatibility with SQL LIKE behavior is required, but it incurs higher development and maintenance costs.

In practice, it is advised to select a solution based on the following guidelines:

• If patterns are simple (using only % at the beginning or end), use string methods.
• If patterns are complex (mixing % and _), use the regex-based solution.
• If full compatibility with a specific database's LIKE behavior is mandatory, consider a custom algorithm.

Conclusion

Implementing SQL LIKE functionality in C# can be achieved through various methods, each with its pros and cons. The regex-based solution, by converting LIKE patterns to regex, provides powerful and flexible pattern matching capabilities, making it an ideal choice for most scenarios. For simple needs, string methods offer an efficient solution; for high compatibility requirements, custom algorithms are necessary. Developers should choose the most suitable solution based on specific needs, balancing performance, complexity, and functionality. Through this exploration, readers can better understand and apply these techniques to enhance their string processing capabilities.