Solved

Regex for a SQL INSERT statement

Posted on 2006-11-07
9
197 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
Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

 
LVL 19

Accepted Solution

by:
VoteyDisciple earned 250 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

Creating Instructional Tutorials  

For Any Use & On Any Platform

Contextual Guidance at the moment of need helps your employees/users adopt software o& achieve even the most complex tasks instantly. Boost knowledge retention, software adoption & employee engagement with easy solution.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Access Schema 6 53
Vb.net progressbar with timer1 in visual studio win app 11 49
How can I add google maps in vb.net. 2 42
VB6 calling COM DLL written in Visual Studio 2003 6 52
Introduction As chip makers focus on adding processor cores over increasing clock speed, developers need to utilize the features of modern CPUs.  One of the ways we can do this is by implementing parallel algorithms in our software.   One recent…
It’s quite interesting for me as I worked with Excel using vb.net for some time. Here are some topics which I know want to share with others whom this might help. First of all if you are working with Excel then you need to Download the Following …
I've attached the XLSM Excel spreadsheet I used in the video and also text files containing the macros used below. https://filedb.experts-exchange.com/incoming/2017/03_w12/1151775/Permutations.txt https://filedb.experts-exchange.com/incoming/201…

740 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