title: SQL Server - Multi-Column substring matching
body: <p>One of my clients is hooked on multi-column substring matching.</p>
  
  <p>I understand that <a href="http://msdn.microsoft.com/en-us/library/ms187787.aspx" rel="nofollow noreferrer">Contains</a> and <a href="http://msdn.microsoft.com/en-us/library/ms176078.aspx" rel="nofollow noreferrer">FreeText</a> search for words (and at least in the case of Contains, word prefixes). However, based upon my understanding of <a href="http://msdn.microsoft.com/en-us/library/ms142559.aspx" rel="nofollow noreferrer">this</a> MSDN book, neither of these nor their variants are capable of searching substrings. </p>
  
  <p>I have used <a href="http://msdn.microsoft.com/en-us/library/ms179859.aspx" rel="nofollow noreferrer">LIKE</a> rather extensively (Select * from A where A.B Like '%substr%')</p>
  
  <p>Sample table A:</p>
  
  <pre><code>ID | Col1     | Col2     | Col3     |
  -------------------------------------
  1  | oklahoma | colorado | Utah     |
  2  | arkansas | colorado | oklahoma |
  3  | florida  | michigan | florida  |
  -------------------------------------
  </code></pre>
  
  <p>The following code will give us row 1 and row 2:</p>
  
  <pre><code> select * from A where Col1 like '%klah%' or Col2 like '%klah%' or Col3 like '%klah%'
  </code></pre>
  
  <p>This is rather ugly, probably slow, and I just don't like it very much. Probably because the implementations that I'm dealing with have 10+ columns that need searched.</p>
  
  <p>The following may be a slight improvement as code readability goes, but as far as performance, we're still in the same ball park.</p>
  
  <pre><code> select * from A where (Col1 + ' ' + Col2 + ' ' + Col3) like '%klah%'
  </code></pre>
  
  <p>I have thought about simply adding insert, update, and delete triggers that simply add the concatenated version of the above columns into a separate table that shadows this table.</p>
  
  <p>Sample Shadow_Table:</p>
  
  <pre><code>ID | searchtext                 |
  ---------------------------------
  1  | oklahoma colorado Utah     |
  2  | arkansas colorado oklahoma |
  3  | florida michigan florida   |
  ---------------------------------
  </code></pre>
  
  <p>This would allow us to perform the following query to search for '%klah%'</p>
  
  <pre><code>select * from Shadow_Table where searchtext like '%klah%'
  </code></pre>
  
  <p>I really don't like having to remember that this shadow table exists and that I'm supposed to use it when I am performing multi-column substring matching, but it probably yields pretty quick reads at the expense of write and storage space.</p>
  
  <p>My gut feeling tells me there there is an existing solution built into SQL Server 2008. However, I don't seem to be able to find anything other than research papers on the subject. </p>
  
  <p>Any help would be appreciated.</p>