RegEx for parsing SQL query in C#

Question

I need to write RegEx in C# to parse SQL join query which is given as a string. Can somebody please help me because I'm new at this. Thanks a lot

this is my problem:

string query = @"SELECT table1.column1, table1.column2, table2.coulmn1, table2.column2
                 FROM table1 INNER JOIN table2 ON table1.column5 = table2.column5";

what I actually need is to put all important data into separate variables, like this:

string class1 = table1    
string class2 = table2    
string joinForeignKey1 = table1.column5    
string joinForeignKey2 = table2.column5    
List<string> attributes1 = table1.column1, table1.column2    
List<string> attributes2 = table2.column1, table2.column2

//COMMENT

I realized that I have made a mistake in sql query so there will be an ON clause.

I can force a user to provide me with the correct syntax, so that will be no problem.

The thing I haven't mentioned is that there can be more than one JOIN ON clause (multiple joins).

Thanks a lot and I will appreciative any given help.

Answer 1

I personally do not recommend doing this unless you have a VERY, VERY valid reason to do so as well as full control over the way that the SQL would be written.

First and foremost the syntax that you noted for the SQL statement is the old style join syntax and not using the more common ON syntax.

Something like

SELECT A.ColumnA, B.ColumnB
FROM MyTable A
    INNER JOIN YourTable B
        ON (A.MyIdentity = B.MyForeignKey)

So unless you can force users to input queries in the old syntax you are already going down the road to a way that will not work.

If I was forced to do this type of thing, and I did have control over it, I personally wouldn't bother with RegEx, due to the fact that the process is so structured. i would just use basic string manipulation.

Answer 2

Pulling this over from comments, since I think it's the right answer here:

SQL is #3 on the list of Stuff You Should Not Try To Parse With A Regex, just behind HTML and MUMPS. Use a dialect-specific, dedicated SQL parser, not a regex.