Parse filter expression using RegEx

Question

I have a query filter written in human readable language. I need to parse it and convert to the SQL where clause. Examples are:

CustomerName Starts With 'J'

becomes

CustomerName LIKE 'J%'

and

CustomerName Includes 'Smi'

becomes

CustomerName LIKE '%Smi%'

The full expression to be parsed may be much more complicated such as

CustomerName Starts With 'J' AND State = 'CA'

should become

CustomerName LIKE 'J%' AND State = 'CA'

What is the best way to go about this? Can it be done easily with RegEx (I am not a regex expert).

Answer 1

In the absence of other answers I'll chuck in my 2 cents:

It seems to me that you're actually creating a new language, albeit one that's very similar to an existing one. So I suspect there are many techniques from the field of computer language interpretation that could make this a simpler task.

The other thing to be cautious of are accidental (or intentional) SQL injection problems.

I've not tackled a task like this, but if I were approaching this using Regex I'd be tempted to have a separate method for each new syntax and then pass that input to each of the methods. For example here's how I might handle the 'Start With' syntax.

    public void Main()
    {
        string input = @"CustomerName Starts With 'J' AND State = 'CA'";

        ReplaceStartsWith(ref input);
        //...
        //ReplaceIncludes(ref input);

        Console.WriteLine(input);
        //Returns: CustomerName LIKE 'J%' AND State = 'CA'
    }

    void ReplaceStartsWith(ref string input)
    {
        input = startsWithRegex.Replace(input, match =>
            string.Format("LIKE '{0}%'", match.Groups["literal"]));
    }

     

    static RegexOptions commonOptions =
        RegexOptions.Compiled |
        RegexOptions.ExplicitCapture |
        RegexOptions.IgnoreCase |
        RegexOptions.IgnorePatternWhitespace |
        RegexOptions.Singleline;

     

    static Regex startsWithRegex = new Regex(
        @"\bstarts\s+with\s+'(?<literal>[^']*)'"
        , commonOptions);