Regular Expression To Match Common Sql Syntax?

I was writing some Unit tests last week for a piece of code that generated some SQL statements. I was trying to figure out a regex to match SELECT, INSERT and UPDATE syntax so I co

Solution 1:

Regular expressions can match languages only a finite state automaton can parse, which is very limited, whereas SQL is a syntax. It can be demonstrated you can't validate SQL with a regex. So, you can stop trying.

Solution 2:

SQL is a type-2 grammar, it is too powerful to be described by regular expressions. It's the same as if you decided to generate C# code and then validate it without invoking a compiler. Database engine in general is too complex to be easily stubbed.

That said, you may try ANTLR's SQL grammars.

Solution 3:

As far as I know this is beyond regex and your getting close to the dark arts of BnF and compilers.

http://savage.net.au/SQL/

Same things happens to people who want to do correct syntax highlighting. You start cramming things into regex and then you end up writing a compiler...

Solution 4:

I had the same problem - an approach that would work for all the more standard sql statements would be to spin up an in-memory Sqlite database and issue the query against it, if you get back a "table does not exist" error, then your query parsed properly.

Solution 5:

Have you tried the lazy selectors. Rather than match as much as possible, they match as little as possible which is probably what you need for quotes.