Got more questions? Find advice on: ASP | SQL | XML | Windows
in Search
Welcome to RegexAdvice Sign in | Join | Help

Split SQL WHERE Clause

Last post 10-23-2012, 6:42 PM by Aussie Susan. 15 replies.
Page 1 of 2 (16 items)   1 2 Next >
Sort Posts: Previous Next
  •  03-20-2008, 9:58 PM 40538

    Split SQL WHERE Clause

    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

  •  03-21-2008, 2:29 AM 40543 in reply to 40538

    Re: Split SQL WHERE Clause

    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 

     

     

  •  03-21-2008, 2:08 PM 40561 in reply to 40543

    Re: Split SQL WHERE Clause

    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

  •  03-21-2008, 2:59 PM 40563 in reply to 40561

    Re: Split SQL WHERE Clause

    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]

    Match
    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'''

     

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

  •  03-21-2008, 3:05 PM 40564 in reply to 40563

    Re: Split SQL WHERE Clause

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

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

     

  •  03-21-2008, 3:09 PM 40565 in reply to 40563

    Re: Split SQL WHERE Clause

    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:

    Match$1$2$3$4
    String='57846' String='57846'
    Number=1 Number=1
    Date=#1/1/2008# Date=#1/1/2008#
    Time=#1:34 PM# Time=#1:34 PM#
    DATETIMED = #1/1/2008 1:34:25 PM# DATETIMED=#1/1/2008 1:34:25 PM#
    GTOE>=100 GTOE>=100
    SpecialString='With Aposts SpecialString='With Aposts

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

    Thanks,

    Jake

  •  03-21-2008, 3:14 PM 40566 in reply to 40565

    Re: Split SQL WHERE Clause

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

    Sergei

  •  03-21-2008, 3:21 PM 40567 in reply to 40566

    Re: Split SQL WHERE Clause

    Sergei,

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

    Jake

  •  03-21-2008, 5:42 PM 40573 in reply to 40567

    Re: Split SQL WHERE Clause

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

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

     

    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

  •  03-23-2008, 12:00 PM 40611 in reply to 40573

    Re: Split SQL WHERE Clause

    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. Smile

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

    Jake

  •  03-23-2008, 12:54 PM 40612 in reply to 40611

    Re: Split SQL WHERE Clause

    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
     

     

  •  03-24-2008, 1:16 PM 40629 in reply to 40612

    Re: Split SQL WHERE Clause

    Sergei,

    Thank you again for all your help!

    Jake

  •  10-22-2012, 4:58 AM 86883 in reply to 40563

    Re: Split SQL WHERE Clause

    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!!! 

  •  10-22-2012, 5:48 PM 86887 in reply to 86883

    Re: Split SQL WHERE Clause

    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

  •  10-23-2012, 1:03 AM 86891 in reply to 86887

    Re: Split SQL WHERE Clause

    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. 

Page 1 of 2 (16 items)   1 2 Next >
View as RSS news feed in XML