We help IT Professionals succeed at work.

We've partnered with Certified Experts, Carl Webster and Richard Faulkner, to bring you a podcast all about Citrix Workspace, moving to the cloud, and analytics & intelligence. Episode 2 coming soon!Listen Now

x

Regex for a SQL INSERT statement

rbichon
rbichon asked
on
Medium Priority
234 Views
Last Modified: 2008-02-26
I am trying to write a regex to validate an insert statement. I am writing it in pieces. The part I am having trouble with is the VALUES area of the sql statement. The problem is that any value will either be enclosed in single quotes or may be NULL. I am having trouble getting the syntax correct on this one. Can someone assist in this? Thanks.
Comment
Watch Question

The fact that quotes can be escaped inside quotes makes this a little tricky, but I can mentally construct a Deterministic Finite Automata that does it, so can therefore prove that there exists a regular expression to do it.  (Hey, who says all those theory classes in grad school were for naught?)

I think the basic structure for each value is gonna have to be:

(a_value_in_quotes | an_expression | NULL)


To handle a value in quotes I'm thinking this will do the trick:
' (\.|[^'\])* '

That is, between two quotes you can have either any character preceded by a backslash, or any character other than a quote.

It's the "expression" part that gets really tricky to the point that I'm pretty sure expressions (even in SQL) are context free, not regular.  If you're forbidding expressions then you're probably set.
Heh, I forgot to put the "a value" regexp together into "a list of values".

I can't think of a way to write this without repeating the regexp twice, but... it'll have to do.  (-:

\(
    ( ' ( \.  |   [^'\] )* ' | NULL)
    (
        ,[\s]*
        (' ( \.  |   [^'\] )* ' | NULL)
    )*
\)

That should get a list of values separated by commas, yet forbidding an extra comma at the beginning or end.

Author

Commented:
That is true. The regex has to be repeated because of the comma. I think you have the '\ backwards though. Shouldn't it be \'? Otherwise you are escaping the bracket which will cause an error. I will test this at work tomorrow. Thanks for your help.
Yeah, some of my backslashes are meant to escape regexp characters and some are supposed to be literal backslashes.  Do you mean the parser can't tell the difference on its own?  (-;

Let's try that again:

\(
    ( ' ( \\.  |   [^'\\] )* ' | NULL)
    (
        ,[\s]*
        (' ( \\.  |   [^'\\] )* ' | NULL)
    )*
\)

Not the solution you were looking for? Getting a personalized solution is easy.

Ask the Experts

Author

Commented:
Your answer was close in theory but lacks a certain level of experience. Any space or line break you put in a regular expression is usually interpreted as a part of the expression.
Sure, you can either set the parser to ignore whitespace in the pattern, which means you can keep the more human-readable format, or you can (as I expected you would) remove the whitespace when actually coding it, on the (probably valid) assumption that nobody reading it in the future is gonna understand it without mentally parsing it anyway.

Suggesting I lack experience because I sought to produce a more legible version of the code is rude, incidentally; there's no need to insult me or my intelligence.

Author

Commented:
I don't mean to insult but there are several obvious syntax errors in the code. And I might be wrong but I have never seen a way to configure the parser to accept the expression in the form that you have it. Long story short, your solution did not work on many levels.
I see.  I tested with a PCRE parser, where that syntax is perfectly valid; I know Microsoft imposes some odd rules on its regexp engine in .NET but I didn't realize it would hiccup on something so straightforward.

Sorry I couldn't be of more direct help.
Access more of Experts Exchange with a free account
Thanks for using Experts Exchange.

Create a free account to continue.

Limited access with a free account allows you to:

  • View three pieces of content (articles, solutions, posts, and videos)
  • Ask the experts questions (counted toward content limit)
  • Customize your dashboard and profile

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.