title: How can I search a SQL database with multiple "%" wildcards?
body: <p>I am trying to write a SQL query in SQL Server 2008 R2 that will allow a user to search a database table by a number of parameters. The way this should work is, my user enters his criteria and the query looks for all close matches, while ignoring those criteria for which the user did not enter a value.</p>
  
  <p>I've written my query using <code>LIKE</code> and parameters, like so:</p>
  
  <pre><code>select item 
  from [item] 
  where a like @a and b like @b and c like @c ...
  </code></pre>
  
  <p>where '<code>a</code>', '<code>b</code>', and '<code>c</code>' are table columns, and my <code>@</code> parameters all default to '<code>%</code>' wildcards. This goes on for about twenty different columns, and that leads to my problem: if this query is entered as is, no input, just wildcards, it returns no results. Yet this table contains over 30,000 rows, so an all-wildcard query should return the whole table. Obviously I'm going about this the wrong way, but I don't know how to correct it.</p>
  
  <p>I can't use '<code>contains</code>' or '<code>freetext</code>', as those look for whole words, and I need to match user input no matter where it occurs in the actual column value. I've tried breaking my query up into individual steps using 'intersect', but that doesn't change anything. Does anyone know a better way to do this?</p>