Loading ...
Sorry, an error occurred while loading the content.

Regular Expression Help

Expand Messages
  • Alessandro Antonello
    Hi, all. I have a problem building a regular expression for fields in a SQL UPDATE statement. The SET clause has the format as follows: field1 = value of
    Message 1 of 6 , Mar 31 10:35 PM
    • 0 Attachment
      Hi, all.

      I have a problem building a regular expression for fields in a SQL UPDATE
      statement. The SET clause has the format as follows:

      field1 = 'value of field1', field2 = 'field\' 2 value'

      I have built a regular expression that can split the name of fields from its
      values. But when a value has an escaped single-quote the regular expression
      fails. I know that it fails because I am using the expression [^'] in between
      the parenthesis. But how to fix this?

      This is the regular expression I am using:

      /(\w+)\s*=\s*(('[^']*'\s*,)|(\d+\s*,))\s*/i

      Could someone help me with that?

      Alessandro Antonello

      --
      You received this message from the "vim_use" maillist.
      Do not top-post! Type your reply below the text you are replying to.
      For more information, visit http://www.vim.org/maillist.php
    • Tony Mechelynck
      ... I think you could use non-greedy matching i.e. . {-} and end at (or maybe just before) a single quote not preceded by a backslash (maybe end the pattern
      Message 2 of 6 , Mar 31 11:17 PM
      • 0 Attachment
        On 01/04/11 07:35, Alessandro Antonello wrote:
        > Hi, all.
        >
        > I have a problem building a regular expression for fields in a SQL UPDATE
        > statement. The SET clause has the format as follows:
        >
        > field1 = 'value of field1', field2 = 'field\' 2 value'
        >
        > I have built a regular expression that can split the name of fields from its
        > values. But when a value has an escaped single-quote the regular expression
        > fails. I know that it fails because I am using the expression [^'] in between
        > the parenthesis. But how to fix this?
        >
        > This is the regular expression I am using:
        >
        > /(\w+)\s*=\s*(('[^']*'\s*,)|(\d+\s*,))\s*/i
        >
        > Could someone help me with that?
        >
        > Alessandro Antonello
        >

        I think you could use non-greedy matching i.e. .\{-} and end at (or
        maybe just before) a single quote not preceded by a backslash (maybe end
        the pattern with [^\\]' if there must be at least one character between
        the quotes).

        See
        :help non-greedy
        :help /[]


        Best regards,
        Tony.
        --
        Loose bits sink chips.

        --
        You received this message from the "vim_use" maillist.
        Do not top-post! Type your reply below the text you are replying to.
        For more information, visit http://www.vim.org/maillist.php
      • Terence Monteiro
        Alessandro, On Fri, Apr 1, 2011 at 11:05 AM, Alessandro Antonello
        Message 3 of 6 , Mar 31 11:23 PM
        • 0 Attachment
          Alessandro,

          On Fri, Apr 1, 2011 at 11:05 AM, Alessandro Antonello <antonello.ale@...> wrote:
          Hi, all.

          I have a problem building a regular expression for fields in a SQL UPDATE
          statement. The SET clause has the format as follows:

          field1 = 'value of field1', field2 = 'field\' 2 value'

          I have built a regular expression that can split the name of fields from its
          values. But when a value has an escaped single-quote the regular expression
          fails...

          This should work:

          /\(\w\+\)\s*=\s*\(\(\\'\|[^']\)*\)'

          1. You need to escape the +, (, ), | metacharacters
          2. The value part is 0 or more \' or [^'] sequences
           
          Regards, Terence.

          --
          You received this message from the "vim_use" maillist.
          Do not top-post! Type your reply below the text you are replying to.
          For more information, visit http://www.vim.org/maillist.php
        • Terence Monteiro
          ... You ll need a after the s*. Depending on whether you want to capture the single quotes: / ( w + ) s*= s* ( |[^ ] )* Regards, Terence. -- You
          Message 4 of 6 , Mar 31 11:29 PM
          • 0 Attachment
            On Fri, Apr 1, 2011 at 11:53 AM, Terence Monteiro wrote:
            /\(\w\+\)\s*=\s*\(\(\\'\|[^']\)*\)'

            You'll need a ' after the \s*. Depending on whether you want to capture the single quotes:

            \(\w\+\)\s*=\s*'\(\\'\|[^']\)*'

            Regards, Terence.

            --
            You received this message from the "vim_use" maillist.
            Do not top-post! Type your reply below the text you are replying to.
            For more information, visit http://www.vim.org/maillist.php
          • Jürgen Krämer
            Hi, ... there are some problems with your regular expression: first, Vim needs a lot of backslashes where Perl or other regexp variants do not. You can fix
            Message 5 of 6 , Apr 1, 2011
            • 0 Attachment
              Hi,

              Alessandro Antonello wrote:
              >
              > I have a problem building a regular expression for fields in a SQL UPDATE
              > statement. The SET clause has the format as follows:
              >
              > field1 = 'value of field1', field2 = 'field\' 2 value'
              >
              > I have built a regular expression that can split the name of fields from its
              > values. But when a value has an escaped single-quote the regular expression
              > fails. I know that it fails because I am using the expression [^'] in between
              > the parenthesis. But how to fix this?
              >
              > This is the regular expression I am using:
              >
              > /(\w+)\s*=\s*(('[^']*'\s*,)|(\d+\s*,))\s*/i
              >
              > Could someone help me with that?

              there are some problems with your regular expression: first, Vim needs a
              lot of backslashes where Perl or other regexp variants do not. You can
              fix this by putting \v inside your regexp (see ":help /\v"). As a
              consequence you now have to prefix the equal sign with a backslash:

              /\v(\w+)\s*\=\s*(('[^']*'\s*,)|(\d+\s*,))\s*/i

              Now you want to match single quoted strings which might include a
              backlash-escaped single quote. That means "'[^']'*" is not enough;
              inside your string everything but single quotes should be allowed, or
              alternative a backslash-escaped single quote. This leads us to
              "'([^']|\\')*'" for a string:

              /\v(\w+)\s*\=\s*(('([^']|\\')*'\s*,)|(\d+\s*,))\s*/i

              This still won't match you example, because "\s*," is expected after
              every string or number. First lets move them out of the parentheses:

              /\v(\w+)\s*\=\s*(('([^']|\\')*')|(\d+))\s*,\s*/i

              Now you have to decide: do you want this regular expression to match a
              single field or a list of fields. A single field is (almost) easy: just
              strip "\s*,\s*" from the end:

              /\v(\w+)\s*\=\s*(('([^']|\\')*')|(\d+))/i

              Both fields in your example get matched now, but the second match is too
              short. This is due to the way alternatives are tested. You can fix this
              by swapping "[^']" and "\\'":

              /\v(\w+)\s*\=\s*(('(\\'|[^'])*')|(\d+))/i

              This correctly matches both fields. Before we continue lets get rid of
              some superfluous parentheses. You don't need them around the
              alternatives as long as the whole alternation is wrapped with
              parentheses:

              /\v(\w+)\s*\=\s*('(\\'|[^'])*'|\d+)/i

              If you want to match a list of fields you must consider that there might
              be no trailing comma after the last field of the list or -- expressed in
              a slightly different way -- that every field has a leading comma except
              the first one. For a list only one field is needed; more fields are
              optional and separated from their predecessors with (optional) whitespace,
              a comma, and (again optional) whitespace:

              /\v(\w+)\s*\=\s*('(\\'|[^'])*'|\d+)(\s*,\s*\v(\w+)\s*\=\s*('(\\'|[^'])*'|\d+))*

              That's it.

              Regards,
              Jürgen


              --
              Sometimes I think the surest sign that intelligent life exists elsewhere
              in the universe is that none of it has tried to contact us. (Calvin)

              --
              You received this message from the "vim_use" maillist.
              Do not top-post! Type your reply below the text you are replying to.
              For more information, visit http://www.vim.org/maillist.php
            • Alessandro Antonello
              ... Man, you are a genius! Thanks a lot! -- You received this message from the vim_use maillist. Do not top-post! Type your reply below the text you are
              Message 6 of 6 , Apr 1, 2011
              • 0 Attachment
                2011/4/1 Jürgen Krämer <jottkaerr@...>:
                > /\v(\w+)\s*\=\s*('(\\'|[^'])*'|\d+)/i

                Man, you are a genius!

                Thanks a lot!

                --
                You received this message from the "vim_use" maillist.
                Do not top-post! Type your reply below the text you are replying to.
                For more information, visit http://www.vim.org/maillist.php
              Your message has been successfully submitted and would be delivered to recipients shortly.