How to index small words (3 letters) with SQL Full-text search?

Question

I have an Incident table with one row that has the value 'out of office' in the Description column.

However the following query does not return that row.

SELECT * FROM Incident
WHERE CONTAINS( (Incident.Description), '"out*"' )

The word 'out' is not in the noise file (I cleared the noise file completely and I rebuilt the index).

Is it because SQL Full-text search does not index small words? Is there a setting for that? Is there a command that I can run to see exactly which noise file has been used to build the index?

Note: I'm on SQL 2005.

Answer 1

If you haven't already, try rebuilding the index after clearing out the noise file, as mentioned in this answer which links to a helpful blog post.

Answer 2

ignore this solution.

Answer 3

Hi, when you deal with the noise files, they are not considered until you restart the propper SQL Server FullText Search service for your instance.

I have tested your scenario doing the following and it works...

---

1. Having the noise file with the value 'out' in it
2. Insert records with value like '% out %' in them
3. Executed your select statement and see that they are not returned
4. Modify the word file and remove the 'out' entry
5. Restarted the SQL Server FullText Search service for my instance
6. Rebuild the full text catalog using "ALTER FULLTEXT CATALOG [NameOfMyCatalog] REBUILD"
7. Wait a bit (give a chance to the full text service to do the job)
8. Executed your select statement and see that they are now returned

---

Hope this helps...