title: Using negative-lookahead (or lookbehind?) to extract column names from SQL
body: <p>I have a long SQL script and I want to identify occurrences of SQL column names and replace them with entries from a dictionary, however I want to preserve parameter names.</p>
  
  <p>A column name has the form <code>schema.TableName.ColumnName</code>, <code>TableName.ColumnName</code> or just <code>ColumnName</code>. Whereas parameter names always have the form <code>@parameterName</code>.</p>
  
  <p>So given this script (contrived example):</p>
  
  <pre><code>DECLARE @foo varchar(max) = '123'
  DECLARE @bar varchar(max) = '456'
  SELECT foo, table.bar, @bar FROM table ORDER BY table.foo DESC
  </code></pre>
  
  <p>I want to match:</p>
  
  <pre><code>foo
  table.bar
  table
  table.foo
  </code></pre>
  
  <p>I first wrote a simple regex to match column names:</p>
  
  <pre><code>([A-Za-z_]+[0-9A-Za-z_]*)(\.[A-Za-z_]+[0-9A-Za-z_])*(\.[A-Za-z_]+[0-9A-Za-z_])*
  </code></pre>
  
  <p>(It's a bit of a hack as it builds left-to-right, so the first matched group is either the column-name (if single-labelled), the table-name (if double-labelled), or the schema name (if fully-qualified), but this isn't a huge problem).</p>
  
  <p>...except this regex also selects the portion of parameters immediately after the at-symbol. So I need to modify it so it doesn't match parameters. I added a negative-lookbehind assertion <code>(?&lt;!\@)</code> to match the leading <code>@</code> prefix and to then cancel the match, however it doesn't work:</p>
  
  <pre><code>((?&lt;!\@)([A-Za-z_]+[0-9A-Za-z_]*)(\.[A-Za-z_]+[0-9A-Za-z_])*(\.[A-Za-z_]+[0-9A-Za-z_])*
  </code></pre>
  
  <p>Despite the negative-lookbehind assertion, given the input "<code>@foobar</code>" it matches/captures "<code>@f[oobar]</code>" instead of refusing to capture it at all.</p>
  
  <p>Obviously I'm not using the lookbehind assertion correctly. I've tried placing the assertion in and outside of the parent group and also tried the negative-lookahead assertion, to no effect.</p>