I am pretty new to this Reg Exp stuff, but gaining more understanding every day.  My ASP application is attempting to Split a WHERE clause on the " AND "s, but having no luck.  I would also one day like to expand it to be able to split on " OR " but for now I settle with the baby step.

Expression: (\w+)\s*([<>=]+)\s*([^AND$]+)
Test Value: String='57846' AND Number=1 AND Date=#1/1/2008# AND Time=#1:34 PM# AND DATETIMED = #1/1/2008 1:34:25 PM# AND GTOE>=100 AND SpecialString='With Aposts And Spaces ''4'''

The matches return:
"String='57846' "
"Number=1 "
"Date=#1/1/2008# "
"Time=#1:34 PM# "
"DATETIMED = #1/1/2008 1:34:25 PM# "
"GTOE>=100 "
"SpecialString='With "

All returns are great, except for the last, which I need to be:
"SpecialString='With Aposts And Spaces ''4''"

Thanks for the help,

Jake

JHulse,

Your expression isn't doing what you think it is doing.  I assume that ([^AND$]+) is supposed to be looking for anything but the word "AND".  What it really matches is any string that doesn't contain the characters 'A', 'N', 'D', or '$'.  This is why your last match isn't what you expect.  It stops with it hits the 'A' in "Aposts".  The expression should be ((?!\bAND\b).)+, or ((?!\b(AND|OR)\b).)+ if you want to split on ORs as well.

I don't know exactly what language or library you are using, so I don't know if lookahead assertions like the one I use in my expression are supported.  Either way, you might have better luck just calling your library's Split function, if it has one.  If you happen to be using ASP.Net (which does support lookahead), it would look like this:

Regex.Split( text, @"\s*\b(AND|OR)\b\s*" );

Both of these approaches will fail if the right side of the condition contains the text "AND" or "OR":

... WHERE String='This AND That' 

Good luck.

Jeff 

Jeff, Thank you for your reply, and you are absolutly correct on my assumption.  Your expression helps me a lot, but it seems to be returning some strange results:

"String='57846' "
"ND Number=1 "
"ND Date=#1/1/2008# "
"ND Time=#1:34 PM# "
"ND DATETIMED = #1/1/2008 1:34:25 PM# "
"ND GTOE>=100 "
"ND SpecialString='With Aposts "
"nd Spaces ''4'''"

I know that the last line is getting returned because your expression cannot tell the difference between the 2 types of AND, which is fine.  The ND in the beginning is a bit strange though.  Also, there is another requirement that my application has that I forgot to mention, I need to pull the Field name out in a submatch.  Using your example I have come up with

(\w+)\s*([=<>]+)((?!\bAND\b).)+

Which is giving me the following returns:

"String='57846' "
    "String"
    "="
    " "
"Number=1 "
    "Number"
    "="
    " "
"Date=#1/1/2008# "
    "Date"
    "="
    " "
"Time=#1:34 PM# "
    "Time"
    "="
    " "
"DATETIMED = #1/1/2008 1:34:25 PM# "
    "DATETIMED"
    "="
    " "
"GTOE>=100 "
    "GTOE"
    ">="
    " "
"SpecialString='With Aposts Spaces ''4'''"
    "SpecialString"
    "="
    "'"

This is close, but I need the 3rd submatch to be the filter value.

Thank you so much for your help Jeff and to all you RegEx gurus!

Jake

this regex

AND(?:[^']|'(?:[^']|'{2})+')*?(?=AND|$|\Z) 

run vs

String='57846' AND Number=1 AND Date=#1/1/2008# AND Time=#1:34 PM# AND DATETIMED = #1/1/2008 1:34:25 PM# AND GTOE>=100 AND SpecialString='With Aposts And Spaces ''4'''

will give you the following matches: [tested in http://regexlib.com/RETester.aspx]

try to modify the regex to get rif of 'AND in the matches and pick up the first string too.

(?<=^|\A|AND)(?:[^']|'(?:[^']|'{2})+')*?(?=AND|$|\Z)

Sergei,

That is almost exaclty what I am looking for.  Now I need it to return submatches of the Field name, opperator, then filter value.  Any chance you could take a crack at modifying your expression to do that?  There is no chance i can.  The following expression works, but still has the AND problem:

(\w+)\s*([=<>]+)(((?!\bAND\b).)+)

Tested with that tester I got:

As you can see we still have the and problem, but the sub results are perfect for me.

Thanks,

Jake

i'll try after work at 8 pm EST (US East Coast time)

Sergei

Sergei,

Thank you so much, it is a tremendous help!  I'll keep you posted if I blindly stumble across a solution.

Jake

(?<=^|\A|AND)\s*(\w+)\s*([=<>]+)\s*((?:[^']|'(?:[^']|'{2})+')*?)\s*(?=AND|$|\Z)

run vs:

String='57846' AND Number=1 AND Date=#1/1/2008# AND Time=#1:34 PM# AND DATETIMED = #1/1/2008 1:34:25 PM# AND GTOE>=100 AND SpecialString='With Aposts And Spaces ''4''' AND GTOE>=100

Sergei,

Absolutly PERFECT!  This is exactly what i was looking for, thank you so much for your time.  Since I do plan to continue with regular expressions, and I want to be able to do it without coming here to ask you to do my work for me, can you recomend any sites or reading that go in depth?  Looking at the expression you created, i understand maybe 30%, so obviously http://regexlib.com/CheatSheet.aspx is missing quite a few special characters.

Thank you again for what I'm sure was a lot of work,

Jake

best textbook on regex is the famous Jeffrey Friedl *Mastering Regular Expressions' Sec Edition from O'Reilly. This is a must. This site could be a good quick reference source as many others.

Below is RegexBuddy's generated brake-down of the regex I offered, it sh help you understand the logic of it

happy regexing to you!

(?<=^|\A|AND)\s*(\w+)\s*([=<>]+)\s*((?:[^']|'(?:[^']|'{2})+')*?)\s*(?=AND|$|\Z)

Assert that the regex below can be matched, with the match ending at this position (positive lookbehind) «(?<=^|\A|AND)»
   Match either the regular expression below (attempting the next alternative only if this one fails) «^»
      Assert position at the start of the string «^»
   Or match regular expression number 2 below (attempting the next alternative only if this one fails) «\A»
      Assert position at the start of the string «\A»
   Or match regular expression number 3 below (the entire group fails if this one fails to match) «AND»
      Match the characters "AND" literally «AND»
Match a single character that is a "whitespace character" (spaces, tabs, line breaks, etc.) «\s*»
   Between zero and unlimited times, as many times as possible, giving back as needed (greedy) «*»
Match the regular expression below and capture its match into backreference number 1 «(\w+)»
   Match a single character that is a "word character" (letters, digits, etc.) «\w+»
      Between one and unlimited times, as many times as possible, giving back as needed (greedy) «+»
Match a single character that is a "whitespace character" (spaces, tabs, line breaks, etc.) «\s*»
   Between zero and unlimited times, as many times as possible, giving back as needed (greedy) «*»
Match the regular expression below and capture its match into backreference number 2 «([=<>]+)»
   Match a single character present in the list "=<>" «[=<>]+»
      Between one and unlimited times, as many times as possible, giving back as needed (greedy) «+»
Match a single character that is a "whitespace character" (spaces, tabs, line breaks, etc.) «\s*»
   Between zero and unlimited times, as many times as possible, giving back as needed (greedy) «*»
Match the regular expression below and capture its match into backreference number 3 «((?:[^']|'(?:[^']|'{2})+')*?)»
   Match the regular expression below «(?:[^']|'(?:[^']|'{2})+')*?»
      Between zero and unlimited times, as few times as possible, expanding as needed (lazy) «*?»
      Match either the regular expression below (attempting the next alternative only if this one fails) «[^']»
         Match any character that is not a "'" «[^']»
      Or match regular expression number 2 below (the entire group fails if this one fails to match) «'(?:[^']|'{2})+'»
         Match the character "'" literally «'»
         Match the regular expression below «(?:[^']|'{2})+»
            Between one and unlimited times, as many times as possible, giving back as needed (greedy) «+»
            Match either the regular expression below (attempting the next alternative only if this one fails) «[^']»
               Match any character that is not a "'" «[^']»
            Or match regular expression number 2 below (the entire group fails if this one fails to match) «'{2}»
               Match the character "'" literally «'{2}»
                  Exactly 2 times «{2}»
         Match the character "'" literally «'»
Match a single character that is a "whitespace character" (spaces, tabs, line breaks, etc.) «\s*»
   Between zero and unlimited times, as many times as possible, giving back as needed (greedy) «*»
Assert that the regex below can be matched, starting at this position (positive lookahead) «(?=AND|$|\Z)»
   Match either the regular expression below (attempting the next alternative only if this one fails) «AND»
      Match the characters "AND" literally «AND»
   Or match regular expression number 2 below (attempting the next alternative only if this one fails) «$»
      Assert position at the end of the string (or before the line break at the end of the string, if any) «$»
   Or match regular expression number 3 below (the entire group fails if this one fails to match) «\Z»
      Assert position at the end of the string (or before the line break at the end of the string, if any) «\Z»

Created with RegexBuddy
 

Sergei,

Thank you again for all your help!

Jake

hey there,

regex

AND(?:[^']|'(?:[^']|'{2})+')*?(?=AND|$|\Z) 

run vs

String='57846' AND Number=1 AND Date=#1/1/2008# AND Time=#1:34 PM# AND DATETIMED = #1/1/2008 1:34:25 PM# AND GTOE>=100 AND SpecialString='With Aposts And Spaces ''4''' 

 can anyone modify this regex to

 1) pick up the frist string too

 2)  split by keywords "AND" or "OR)

thank!!! 

The second question is straight forward - use the "split()" regex function with the pattern:

\b(and|or)\b

and use the "ignore case" (which is generally the better way to go, or use upper case words to only split on uppercase words).

One point to note is that the resulting strings will contain any whitespace character(s) before or after each string - you may want to use the "trim" string function (or whatever the equivalent is in your programming language).

As for the first question, did you look at the solution 1 reply down from the one where you took that regex pattern (reply #40564)? As far as I can see, that would meet your requirement.

Susan

thanks for you answer. 

the problem with the "split"-function is that the keywords will be lost. I want to keep the keywords in the splitted strings.  

I´m not very well in regex and the regex pattern from the post before would generally meet my requirements.