Need help with Sql Server 2008 and Full Text Searching

Question

Hi folks,

I've setup a FTS on a single field, in a single table.

Field: Name NVARHCHAR(350) NOT NULL

Now, when i search for the following

1 ave
10 ave

i don't get back the results i expect.

Firstly, the search query 1 ave is transformed into "1*" AND "ave*". Now i run my CONTAINS(..) query...

SELECT FooId, Name
FROM [dbo].[Names] 
WHERE CONTAINS(Name, @SearchQuery)

Then, along with the correct results, i also get these incorrect results back...

2 Ave  (a couple of entries .. but they are all unique entires).

So, how did this get retrieved? there is no 1* in that piece of text? Its like .. the number is ignored?

Also - and this is important - i've removed an reference to a stop list AND rebuilt the catalog.

Hmm. I'm so confused. anyone have any suggestions?

Answer 1

The "1" can occur anywhere within the full text search indexed column it doesn't have to be directly before (or even before) the "Ave", is there a 1 somewhere else in that row?

Answer 2

Full text indexing will find derivitaves of words - like if you search for RUN, it might find RUNNING, RAN, RUN, etc.

I wonder if its deciding that 2 is near 1, and returning that as a near match. You should try switching your query to a CONTAINSTABLE query so that you can also evaluate the RANK to determine which of the answers is a closer match. You could then decide on a threshold and filter out any rows that don't meet your criteria as to how close of a match they are.

EDIT: Its not doing the inflection thinking 1 is near 2. I ran a test query on a sample table that looked like this...

PK          Name
1           1 ave
2           10 ave
3           2 ave
4           12 avenue
5           13 avenue
6           100 ave.
7           200 ave
8           210 avenue

Here's the query I ran...

select  *
from    Table_1
where   contains(name, '"1*" and "ave*"')

And here's the results I get...

PK          Name
2           10 ave
4           12 avenue
5           13 avenue
6           100 ave.

The interesting thing here is that the first record in the table isn't found. (I ran this on SQL 2008 Dev edition). Based on those results (where nothing starting with 2 was found) - I'd double-check your query. Maybe post the full text of your entire query, including where the search variable is being set.