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:
Now I'm trying to find the word 3.
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.
So here is the scenario. Suppose I want to find word 3. My table data are as follows:
- <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.
- <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.
- <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>