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

122060Re: Regular Expression Help

Expand Messages
  • Jürgen Krämer
    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
    • Show all 6 messages in this topic