Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 204
  • Last Modified:

Regex for a SQL INSERT statement

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.
0
rbichon
Asked:
rbichon
  • 5
  • 3
1 Solution
 
VoteyDiscipleCommented:
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.
0
 
VoteyDiscipleCommented:
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.
0
 
rbichonAuthor 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.
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
VoteyDiscipleCommented:
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)
    )*
\)
0
 
rbichonAuthor 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.
0
 
VoteyDiscipleCommented:
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.
0
 
rbichonAuthor 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.
0
 
VoteyDiscipleCommented:
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.
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

  • 5
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now