title: Four Wildcards in a Like String Comparison
body: <p>Trying to get my head around using four wildcard characters in a <code>like</code> string comparison. </p>
  
  <pre><code>select 1 where '%'+field1+'%' not like '%'+field2+'%'
  </code></pre>
  
  <p>Is this pointless? Working left to right, field1 must not exist anywhere in '%'+field2+'%', but since a wildcard can be anything doesn't that mean it will always exist? Are any of the wildcard characters in the statement irrelevant?</p>
  
  <p>In my case I'm trying to find where <code>field1</code> (a free text field) doesn't exist in the value from <code>field2</code> (a defined list).</p>
  
  <p><strong>Currently Using</strong></p>
  
  <pre><code>select field1, field2, COUNT(1) as 'Count' from filteredNew_address 
  where field1 like '%Place%' and field1 LIKE '%[0-9]%' 
  and REPLACE(field1, ' ', '') not like REPLACE(field2, ' ', '')
  group by field1, field2
  order by 'count' desc
  </code></pre>
  
  <p>I get the following results;</p>
  
  <pre><code>field1          field2
  Place 6         Place
  Place 12        Place 24
  Place 4         Place 7
  Place 6b        Place 6
  Place 1         Place 7
  Place 2         Outside Country
  ...
  </code></pre>
  
  <p>Out of the returned values there, the only one that is an issue is the Place 6b record. This should match to the field2 record and so should not be returned.</p>
  
  <p>To correct this I tried changing to;</p>
  
  <pre><code>and REPLACE(field1, ' ', '') not like REPLACE(field2, ' ', '')
  </code></pre>
  
  <p>But this removed the first result (Place 6, Place) which should be returned.</p>