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.

Will all of the queries be in exactly this format? Will there be any possibility that comments might exist in the source or derived tables or any other constructs that will mess things up? Edit: I just noticed what you posted is not valid SQL anyway. It is missing an ON clause. — Martin Smith
– Martin Smith, Commented Jul 27, 2010 at 13:47
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. — Craig Stuntz
– Craig Stuntz, Commented Jul 27, 2010 at 13:53
@martin - His query is valid SQL, just not using the most current ANSI standard for Joins. — Mitchel Sellers
– Mitchel Sellers, Commented Jul 27, 2010 at 13:54
@Mitchel: No, he's mixing SQL-92 and SQL-89 style joins into a hybrid which won't work in either of them. SQL-89 would use table1, table2, not the JOIN keyword. — Craig Stuntz
– Craig Stuntz, Commented Jul 27, 2010 at 13:56

Community · Accepted Answer · 2017-05-23 12:19:41Z

4

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.

edited May 23, 2017 at 12:19

CommunityBot

11 silver badge

answered Jul 27, 2010 at 14:59

Craig Stuntz

127k12 gold badges257 silver badges275 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Mitchel Sellers · Accepted Answer · 2010-07-27 13:53:27Z

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.

Collectives™ on Stack Overflow

RegEx for parsing SQL query in C#

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related