title: Why can I not use character ranges ('[a-z]') in SQL 'LIKE' queries on an ODBCConnection object in Excel 2010?
body: <p>I have an Excel worksheet containing a list which is populated by using the list's ODBC connection in a macro, using this property:</p>
  
  <pre><code>ListObject.QueryTable.WorkbookConnection.ODBCConnection
  </code></pre>
  
  <p>It is configured to connect to its own workbook, because I have another worksheet containing a large amount of data:</p>
  
  <pre><code>ODBCConnection.Connection = "ODBC;DSN=Excel Files;" &amp; _
      "DBQ=" &amp; ThisWorkbook.FullName &amp; ";" &amp; _
      "DriverId=1046;MaxBufferSize=2048;PageTimeout=5;"
  </code></pre>
  
  <p>Then, to utilise this connection, a SQL query can be made, e.g.:</p>
  
  <pre><code>ODBCConnection.CommandText = _
  "SELECT Column1 FROM [Worksheet2$] WHERE Column2 LIKE '%query%'"
  </code></pre>
  
  <p>The issue lies with the fact that these SQL queries do not match the SQL Server 2008 standard. In this article (<a href="https://msdn.microsoft.com/en-us/library/ms179859.aspx" rel="nofollow">https://msdn.microsoft.com/en-us/library/ms179859.aspx</a>) it is stated that a LIKE expression may contain character ranges, e.g. <code>[a-z]</code> or <code>[^a-z]</code>. These do not operate as expected with the ODBC connection and are interpreted literally.</p>
  
  <p>For example, the following query will return 0 records:</p>
  
  <pre><code>ODBCConnection.CommandText = _
  "SELECT Column1 FROM [Worksheet2$] WHERE Column2 LIKE '%[^a-z]keyword[^a-z]%'"
  </code></pre>
  
  <p>If 'keyword' is a common keyword in Column1, this should return many records, as it searches for the keyword surrounded by non-letter characters.</p>
  
  <p>Has anyone experienced this and found a workaround? Thanks!</p>