Regex Help on SQL Parse

  • Thread starter Thread starter paul reed
  • Start date Start date
P

paul reed

Hello, I am using regex to parse a SQL statement. So far so good. However, I
want to be able to use "*" on this one because the Where clause might not
exist at all yet I still want to consider that a match (i.e., * means 0,1,
or more). Here is what I have now. I don't get a match if there isn't a
Where clasue...but I need it to:

"(?<wherePortion>WHERE[\W\w\s]+(?=ORDER))|(?<wherePortion>WHERE[\W\w\s]+)"



Thanks, Paul
 
Hi Pail,

Currently I am looking for somebody who could help you on it. We will reply
here with more information as soon as possible.
If you have any more concerns on it, please feel free to post here.


Thanks for your understanding!

Best regards,

Gary Chang
Microsoft Online Partner Support

Get Secure! - www.microsoft.com/security
This posting is provided "AS IS" with no warranties, and confers no rights.
--------------------
 
You might consider using something like this:

(?<wherePortion>WHERE[\W\w\s]+(?=ORDER)|WHERE[\W\w\s]+)?

The alternation can be done within the named group, and adding "?" after the
group will allow it to match the group 0 or 1 times. You can then check the
Success property of the Group to see if the WHERE clause exists if you need
to know.


Brian Davis
http://www.knowdotnet.com
 
Brian,

Thanks...but no cigar. The result of your suggestion does deliver a hit
if there is no where clause but it if there is a where clause it
registers a hit yet nothing ends up in the match field. I also tried
adding a "?" after the first condition as well. Try your suggestion with
any where clause "Where lastname = 'Reed'" to see what I mean.

Thanks,

Paul Reed
www.jacksonreed.com
 
Your test string has "Where" instead of "WHERE". Try it in caps and it
works. If your expression needs to be case-insensitive, then use
RegexOptions.IgnoreCase.

Note that because the entire match is optional, you will receive empty
matches if you use a MatchCollection. For example, when running the
case-insensitive version on your test string, you will get 2 matches:

Match 0 = "Where lastname = 'Reed'"
Match 1 = ""


Brian Davis
http://www.knowdotnet.com
 
Actually, it doesn't work. I do use the Ignore Case...I just didn't
paste it in. Here is my regex expression:

("(?<wherePortion>WHERE[\W\w\s]+(?=ORDER))|(?<wherePortion>WHERE[\W\w\s]
+)?", RegexOptions.IgnoreCase)

When I do this on "Where LastName = 'Reed' " I get a hit but nothing
returned in the match.

Paul Reed
www.jacksonreed.com
 
When I execute this code:

Dim Input As String = "Where LastName = 'Reed'"
Dim Pattern As String =
"(?<wherePortion>WHERE[\W\w\s]+(?=ORDER))|(?<wherePortion>WHERE[\W\w\s]+)?"
Dim re As New Regex(Pattern, RegexOptions.IgnoreCase)
Dim m As Match = re.Match(Input)
Console.WriteLine(m.Value)
Console.WriteLine(m.Groups("wherePortion").Value)


I get this output:

Where LastName = 'Reed'
Where LastName = 'Reed'


I cannot reproduce the problem you are having. Please post the code you use
to get the match, and I will see if there is something I am missing.


Brian Davis
http://www.knowdotnet.com
 
Brian,

Sure..here goes. I am sure it something "STUPID" I am overlooking :-)

Dim whereReg As Regex = New
Regex("(?<wherePortion>WHERE[\W\w\s]+(?=ORDER))|(?<wherePortion>WHERE[\W
\w\s]+)?", RegexOptions.IgnoreCase)
Dim whereM As Match = whereReg.Match(BaseQuery)

If whereM.Success Then
_baseWhereBlock = whereM.Groups("wherePortion").Value
_strBldrBaseWhereBlock = New StringBuilder(_baseWhereBlock)
Else
_baseWhereBlock = ""
_strBldrBaseWhereBlock = New StringBuilder("")
End If

When I execute this code passing in any complete sql statement, the
"whereM.Success" evaluates to true but the result of whereM.Groups
returns nothing back. If I remove the ?, it works find...but then as you
can see in the Else I initialize the string to "" to make things work.

Thanks in advance,

Paul
 
Passing the complete SQL statement is the difference. If you look at the
MatchCollection, you will see an empty match for each letter before the
"where...", then the match with the "where...", then empty matches for each
letter after the where clause. Your new logic works better. Removing the
"?" gets rid of the empty match problem and enables it to work on an entire
SQL statement.

If the match is not successful, then "whereM.Groups("wherePortion").Value"
will return an empty string, so you don't really even need to check for the
success of the match (unless some other logic requires it), though making
the check helps the readability of the code, in my opinion.

You also may want to place "\b" before and after the "WHERE" in the
expression so that "NOWHERE" or "WHEREVER" would not look like part of a
where clause.

I apologize for the confusion - I don't know why I didn't test an entire SQL
statement instead of just the where clause.


Brian Davis
http://www.knowdotnet.com
 
Brian,

Thanks for all your help on this issue this weekend...especially the
advice about \b...that rose up and stung me someplace else after your
mail and luckily I then know how to handle it :-)

Kind regards

Paul Reed
www.jacksonreed.com
 
Back
Top