title: Look for trailing spaces in a table
body: <p>I'd like to know how I can identify trailing spaces in a table. I'm using SQL Server 2008 and create the following table as a test</p>
  
  <pre><code>CREATE TABLE  first_test_name
     ( 
     firstName varchar(255)
      )
  </code></pre>
  
  <p>And then inserted a record like this:</p>
  
  <pre><code>insert into first_test_name (firstName)
  values('Bob')
  </code></pre>
  
  <p>I then tried inserting a space and then adding a new record like this:</p>
  
  <pre><code>insert into first_test_name (firstName)
  values('Bob ') -- just 1 space
  </code></pre>
  
  <p>And for a 3rd time,</p>
  
  <pre><code>insert into first_test_name (firstName)
  values('Bob  ')   -- two spaces used this time.
  </code></pre>
  
  <p>Now if I query for for 'Bob' (no spaces), I still get a count of 3.
  My query was:</p>
  
  <pre><code>select count(*) from first_test_name WHERE firstName = 'Bob'
  </code></pre>
  
  <p>Shouldn't the answer have been 1?</p>
  
  <p>Also, I used <code>sp_help</code> on this table and the value for "Trim Trailing Blanks" is set to no.</p>
  
  <p>So why am I getting a count of 3? I was expecting just 1.</p>
  
  <p>On a related note, if I search using this query</p>
  
  <pre><code>select * from first_test_name
  where firstName like '% '
  </code></pre>
  
  <p>I then get the right answer of two rows found.</p>
  
  <p>So just to reiterate, the question is why I get a count of 3 when searching for 'Bob'.
  Also, what does "Trim Trailing Blanks" mean in this case?</p>