Full text index search using one-character word

I'm using full text index search. There are few cases when for our customer it is important to find words even with only one character. Unfortunately it seems that SQL server ignores such words, although I haven't found assurance for that in docs.

So here is the scenario. Suppose I want to find word 3. My table data are as follows:

  1. <span class="kwd" style="color: blue;">select</span><span class="pln"> * </span><span class="kwd" style="color: blue;">from</span><span class="pln"> test_fts</span><span class="pun" style="color: grey;">;</span><span class="pln">
  2. </span><span class="pln">id txt</span><span class="pln">
  3. </span><span class="com" style="color: green;">----------- --------------------------------------------------</span><span class="pln">
  4. </span><span class="lit">1</span><span class="pln"> </span><span class="lit">3</span><span class="pln"> trees</span><span class="pln">
  5. </span><span class="lit">2</span><span class="pln"> </span><span class="lit">33</span><span class="pln"> trees</span><span class="pln">
  6. </span><span class="lit">3</span><span class="pln"> green trees</span>
<span class="kwd" style="color: blue;">select</span><span class="pln"> * </span><span class="kwd" style="color: blue;">from</span><span class="pln"> test_fts</span><span class="pun" style="color: grey;">;</span><span class="pln">
</span><span class="pln">id          txt</span><span class="pln">
</span><span class="com" style="color: green;">----------- --------------------------------------------------</span><span class="pln">
</span><span class="lit">1</span><span class="pln">           </span><span class="lit">3</span><span class="pln"> trees</span><span class="pln">
</span><span class="lit">2</span><span class="pln">           </span><span class="lit">33</span><span class="pln"> trees</span><span class="pln">
</span><span class="lit">3</span><span class="pln">           green trees</span>

Now I'm trying to find the word 3.

  1. <span class="kwd" style="color: blue;">select</span><span class="pln"> * </span><span class="kwd" style="color: blue;">from</span><span class="pln"> test_fts</span><span class="pln">
  2. </span><span class="kwd" style="color: blue;">where</span><span class="pln"> </span><span class="kwd" style="color: blue;">contains</span><span class="pun" style="color: grey;">(</span><span class="pln">txt</span><span class="pun" style="color: grey;">,</span><span class="pln"> </span><span class="str" style="color: red;">'"3"'</span><span class="pun" style="color: grey;">)</span><span class="pln">
  3. </span><span class="pln">id txt</span><span class="pln">
  4. </span><span class="com" style="color: green;">----------- --------------------------------------------------</span><span class="pln">
  5. </span><span class="pln">Informational</span><span class="pun" style="color: grey;">:</span><span class="pln"> The full-text search condition contained noise word</span><span class="pun" style="color: grey;">(</span><span class="pln">s</span><span class="pun" style="color: grey;">).</span><span class="pln">
  6. </span><span class="pln">
  7. </span><span class="kwd" style="color: blue;">select</span><span class="pln"> * </span><span class="kwd" style="color: blue;">from</span><span class="pln"> test_fts</span><span class="pln">
  8. </span><span class="kwd" style="color: blue;">where</span><span class="pln"> </span><span class="kwd" style="color: blue;">contains</span><span class="pun" style="color: grey;">(</span><span class="pln">txt</span><span class="pun" style="color: grey;">,</span><span class="pln"> </span><span class="str" style="color: red;">'"3*"'</span><span class="pun" style="color: grey;">)</span><span class="pln">
  9. </span><span class="pln">id txt</span><span class="pln">
  10. </span><span class="com" style="color: green;">----------- --------------------------------------------------</span><span class="pln">
  11. </span><span class="lit">2</span><span class="pln"> </span><span class="lit">33</span><span class="pln"> trees</span>
<span class="kwd" style="color: blue;">select</span><span class="pln"> * </span><span class="kwd" style="color: blue;">from</span><span class="pln"> test_fts</span><span class="pln">
</span><span class="kwd" style="color: blue;">where</span><span class="pln"> </span><span class="kwd" style="color: blue;">contains</span><span class="pun" style="color: grey;">(</span><span class="pln">txt</span><span class="pun" style="color: grey;">,</span><span class="pln"> </span><span class="str" style="color: red;">'"3"'</span><span class="pun" style="color: grey;">)</span><span class="pln">
</span><span class="pln">id          txt</span><span class="pln">
</span><span class="com" style="color: green;">----------- --------------------------------------------------</span><span class="pln">
</span><span class="pln">Informational</span><span class="pun" style="color: grey;">:</span><span class="pln"> The full-text search condition contained noise word</span><span class="pun" style="color: grey;">(</span><span class="pln">s</span><span class="pun" style="color: grey;">).</span><span class="pln">
</span><span class="pln">
</span><span class="kwd" style="color: blue;">select</span><span class="pln"> * </span><span class="kwd" style="color: blue;">from</span><span class="pln"> test_fts</span><span class="pln">
</span><span class="kwd" style="color: blue;">where</span><span class="pln"> </span><span class="kwd" style="color: blue;">contains</span><span class="pun" style="color: grey;">(</span><span class="pln">txt</span><span class="pun" style="color: grey;">,</span><span class="pln"> </span><span class="str" style="color: red;">'"3*"'</span><span class="pun" style="color: grey;">)</span><span class="pln">
</span><span class="pln">id          txt</span><span class="pln">
</span><span class="com" style="color: green;">----------- --------------------------------------------------</span><span class="pln">
</span><span class="lit">2</span><span class="pln">           </span><span class="lit">33</span><span class="pln"> trees</span>

OK 3 is a noise word. I edited noise word list, rebuilt catalog and now the warning about noise words is gone, although the query do not return expected rows.

  1. <span class="kwd" style="color: blue;">select</span><span class="pln"> * </span><span class="kwd" style="color: blue;">from</span><span class="pln"> test_fts</span><span class="pln">
  2. </span><span class="kwd" style="color: blue;">where</span><span class="pln"> </span><span class="kwd" style="color: blue;">contains</span><span class="pun" style="color: grey;">(</span><span class="pln">txt</span><span class="pun" style="color: grey;">,</span><span class="pln"> </span><span class="str" style="color: red;">'"3"'</span><span class="pun" style="color: grey;">)</span><span class="pln">
  3. </span><span class="pln">
  4. </span><span class="pln">id txt</span><span class="pln">
  5. </span><span class="com" style="color: green;">----------- --------------------------------------------------</span><span class="pln">
  6. </span><span class="pln">
  7. </span><span class="kwd" style="color: blue;">select</span><span class="pln"> * </span><span class="kwd" style="color: blue;">from</span><span class="pln"> test_fts</span><span class="pln">
  8. </span><span class="kwd" style="color: blue;">where</span><span class="pln"> </span><span class="kwd" style="color: blue;">contains</span><span class="pun" style="color: grey;">(</span><span class="pln">txt</span><span class="pun" style="color: grey;">,</span><span class="pln"> </span><span class="str" style="color: red;">'"3*"'</span><span class="pun" style="color: grey;">)</span><span class="pln">
  9. </span><span class="pln">
  10. </span><span class="pln">id txt</span><span class="pln">
  11. </span><span class="com" style="color: green;">----------- --------------------------------------------------</span><span class="pln">
  12. </span><span class="lit">2</span><span class="pln"> </span><span class="lit">33</span><span class="pln"> trees</span>
<span class="kwd" style="color: blue;">select</span><span class="pln"> * </span><span class="kwd" style="color: blue;">from</span><span class="pln"> test_fts</span><span class="pln">
</span><span class="kwd" style="color: blue;">where</span><span class="pln"> </span><span class="kwd" style="color: blue;">contains</span><span class="pun" style="color: grey;">(</span><span class="pln">txt</span><span class="pun" style="color: grey;">,</span><span class="pln"> </span><span class="str" style="color: red;">'"3"'</span><span class="pun" style="color: grey;">)</span><span class="pln">
</span><span class="pln">
</span><span class="pln">id          txt</span><span class="pln">
</span><span class="com" style="color: green;">----------- --------------------------------------------------</span><span class="pln">
</span><span class="pln">
</span><span class="kwd" style="color: blue;">select</span><span class="pln"> * </span><span class="kwd" style="color: blue;">from</span><span class="pln"> test_fts</span><span class="pln">
</span><span class="kwd" style="color: blue;">where</span><span class="pln"> </span><span class="kwd" style="color: blue;">contains</span><span class="pun" style="color: grey;">(</span><span class="pln">txt</span><span class="pun" style="color: grey;">,</span><span class="pln"> </span><span class="str" style="color: red;">'"3*"'</span><span class="pun" style="color: grey;">)</span><span class="pln">
</span><span class="pln">
</span><span class="pln">id          txt</span><span class="pln">
</span><span class="com" style="color: green;">----------- --------------------------------------------------</span><span class="pln">
</span><span class="lit">2</span><span class="pln">           </span><span class="lit">33</span><span class="pln"> trees</span>

Popular posts from this blog

Could not load file or assembly 'Microsoft.ReportViewer.Common, Version=xx.0.0.0, Culture=neutral, PublicKeyToken='xxx' or one of its dependencies.

C# Crop white space from around the image

The specified version string contains wildcards, which are not compatible with determinism.