title: Query to find record with the most matching columns, where non-matching columns have a space?
body: <p><strong>The Problem</strong></p>
  
  <p>I'm trying to write a stored procedure in SQL Server to find the best matching record.  Given 5 input parameters @A, @B, @C, @D, and @E (all varchar50) that correspond to the 5 columns A, B, C, D, and E in my table, I'd like to find the record with the most matching columns.  Each column that doesn't match in the chosen record should contain a space ' '. </p>
  
  <p>For example, if I have the input "Sony", "PlayStation", "Controller", "Black", "Damaged", and my table contains the following columns:</p>
  
  <pre><code>"Sony"  "Playstation"  "Unit"   "Black"  "Damaged"
  "Sony"  "Playstation"  " "      " "      " "
  </code></pre>
  
  <p>It should return the second row, because 2 parameters match and for the 3 that don't, there are spaces.  I don't want to return the first row because even though 4 parameters match, the middle one does not match and it is not a space.  If it had been a space, the first row would have been the winner.</p>
  
  <p><strong>My Approach</strong></p>
  
  <p>There are various specifics that I cannot reveal, but my basic approach (note that I'm an SQL novice) was to test every combination from MOST specific to LEAST specific.  So my query would look something like this:</p>
  
  <pre><code>-- start with most specific
  SELECT * FROM dbo.Items WHERE
     A = @A
     B = @B
     C = @C
     D = @D
     E = @E
  
  -- if no matches, try next
  IF @@ROWCOUNT = 0
  SELECT * FROM dbo.Items WHERE
     A = @A
     B = @B
     C = SPACE(1)
     D = @D
     E = @E
  
  ... etc.
  </code></pre>
  
  <p>In my case, I only need to really test 16 configurations, because some of the permutations will never exist.  Even so, this seems like a very inefficient way to achieve what I want.  On top of that, it's not even working.  It seems like comparing against spaces is problematic because of some auto-trimming that's going on.  In any case, my current approach seems inefficient and it doesn't work - so I turn to you for help.</p>