title: SQL Server varchar inequalities - apostrophes yield strange results
body: <p>Using SQL Server 2012</p>
  
  <p>Behold the following query similar to one used in my company's code. The name column is of nvarchar(100) type:</p>
  
  <pre><code>select * 
  from foo
  where name &gt;= 'papa d''s' 
      and name &lt; 'papa d''szzzzzz'
  </code></pre>
  
  <p>This gives some unexpected results. In addition to returning any records where name = 'papa d's pizza inc', it also returns records where name = 'papa ds pizza inc'. Notice the missing apostrophe in the second result.</p>
  
  <p>Now look at these two queries:</p>
  
  <pre><code>select 1 where 'papa ds pizza inc' &gt;= 'papa d''s'
  GO
  select 1 where 'papa ds pizza inc' &lt; 'papa d''szzzzzz'
  </code></pre>
  
  <p>Only the first query returns 1, implying that the AND condition in my first query should have failed.</p>
  
  <p>Can someone tell me what's happening here? </p>
  
  <p>Bonus points if you can describe why anyone would query this way as opposed to using a LIKE clause with a wildcard, assuming injection is thwarted with other validations.</p>