Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Regex for a SQL INSERT statement

Posted on 2006-11-07
9
Medium Priority
?
203 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.
0
Comment
Question by:rbichon
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 3
9 Comments
 
LVL 19

Expert Comment

by:VoteyDisciple
ID: 17894220
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
 
LVL 19

Expert Comment

by:VoteyDisciple
ID: 17894260
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
 
LVL 1

Author Comment

by:rbichon
ID: 17894962
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
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
LVL 19

Accepted Solution

by:
VoteyDisciple earned 750 total points
ID: 17895005
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
 
LVL 1

Author Comment

by:rbichon
ID: 18084746
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
 
LVL 19

Expert Comment

by:VoteyDisciple
ID: 18084793
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
 
LVL 1

Author Comment

by:rbichon
ID: 18085223
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
 
LVL 19

Expert Comment

by:VoteyDisciple
ID: 18085414
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

Independent Software Vendors: 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!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This tutorial demonstrates one way to create an application that runs without any Forms but still has a GUI presence via an Icon in the System Tray. The magic lies in Inheriting from the ApplicationContext Class and passing that to Application.Ru…
Article by: Kraeven
Introduction Remote Share is a simple remote sharing tool, enabling you to see, add and remove remote or local shares. The application is written in VB.NET targeting the .NET framework 2.0. The source code and the compiled programs have been in…
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…
Are you ready to place your question in front of subject-matter experts for more timely responses? With the release of Priority Question, Premium Members, Team Accounts and Qualified Experts can now identify the emergent level of their issue, signal…
Suggested Courses

636 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question