title: How to return rows that do not match a value in a column?
body: <p>The Sphinx documentation has a simple example on using SphinxQL:</p>
  
  <pre><code>SELECT * FROM test WHERE MATCH('@title hello @body world')
  </code></pre>
  
  <p>Let's say I add a varchar column to the table called <code>ids</code> which contains integer values separated by something such as a comma. For example:</p>
  
  <pre><code>-------------------------------
  | title | body  | ids         |
  -------------------------------
  | hello | world | 5,251,87580 |
  -------------------------------
  </code></pre>
  
  <p>If I wanted to amend the above example query to select rows only that contain a specific ID, then I <em>think</em> I could simply change it to this:</p>
  
  <pre><code>SELECT * FROM test WHERE MATCH('@title hello @body world @ids 251')
  </code></pre>
  
  <p>Right?</p>
  
  <p>However, what I'd like to do is actually return rows that <em>do not</em> contain a certain ID in the <code>ids</code> column. Is this possible?</p>
  
  <p>EDIT:</p>
  
  <p>I thought I found the answer, as by branched out from the SphinxQL documentation I found under "boolean expressions" or something similar that you can simply put the exact phrase (or in this case, number) in quotes and then put a minus sign in front.</p>
  
  <p>So the example query simply becomes:</p>
  
  <pre><code>SELECT * FROM test WHERE MATCH('@title hello @body world @ids -"251"')
  </code></pre>
  
  <p>I thought it worked, but it doesn't.</p>
  
  <p>If I change the ID I don't want to match from <code>251</code> to <code>11</code>, and the following column exists in the table:</p>
  
  <pre><code>-------------------------------
  | title | body  | ids         |
  -------------------------------
  | hello | world | 5,111,87580 |
  -------------------------------
  </code></pre>
  
  <p>Then this query:</p>
  
  <pre><code>SELECT * FROM test WHERE MATCH('@title hello @body world @ids -"11"')
  </code></pre>
  
  <p>Doesn't return that row because Sphinx is apparently matching "11" inside of "111".</p>
  
  <p>There are my conf settings:</p>
  
  <pre><code>index_field_lengths = 1
  morphology          = stem_en
  min_word_len        = 1
  min_prefix_len      = 1
  prefix_fields       = name
  expand_keywords     = 1
  </code></pre>