Question

DO free text search with SQL server returns unexpected results : a search for 1-1BL6-3006 returns way too much entities, that does not have this string in their full text indexed column. I suppose this is because '-' is interpreted as a special character.

Besides Sql profiler shows that the query executed uses FREETEXTTABLE and not CONTAINSTABLE. In my case :

SELECT *
FROM FREETEXTTABLE([dbo].[SPB.Document], *, '1-1BL6-3006') as ft
INNER JOIN [SPB.Document] d ON (ft.[KEY] = d.[Id]);

returns 154 results.

SELECT *
FROM CONTAINSTABLE([dbo].[SPB.Document], *, '1-1BL6-3006') as ft
INNER JOIN [SPB.Document] d ON (ft.[KEY] = d.[Id]);

returns 3 results. (correct)

Can you :

Use FREETEXTABLE which was used in DO 3.X (or provide an option / alternative method to do this)
Show me how I can replace FREETEXTTABLE by CONTAINSTABLE in generated sql. (the example does not cover this case : is this possible?)

This would also allow me to fix this question : Full text search with *

Accepted Answer

0

Currently DO supports only FREETEXTTABLE-based queries, i.e. it's impossible to make it executing a similar query with CONTAINSTABLE instead (the link you gave explains this).

On the other hand, the problem you face occurs because stemmer splits the string you pass and performs search of its parts. On the other hand, it's pretty easy to disable stemming for the whole passed string - manual says, "If freetext_string is enclosed in double quotation marks, a phrase match is instead performed; stemming and thesaurus are not performed."

Although I'm not sure if this will work on PostgreSQL as well (likely, won't).

Actually, that's why we decided to initially add support only for Query.FreeText: there is no need to develop complex full-text query language abstraction layer (although we anyway developed a tiny one - Query.FreeText, I mean).

answered Oct 09 '10 at 08:01

Alex Yakunin
2971●4●4●12

edited Oct 09 '10 at 08:04

You're right : enclosing the searched terms for with double quotes force a search with exact match.

(Oct 11 '10 at 03:55) olorin

I understand : the support of the more complex sql server COTNAINSTABLE would require an abstraction layer to be able to use the query on another database software (postgres, ...). And possibly a custom implementation for db that does not support full text search and in memory storage.

Is this planned? And with what priority? We would be interested by simple full text language support (search with "" or *) for sql server and maybe for in memory provider.

(Oct 11 '10 at 04:00) olorin

Is this planned? And with what priority?

Until now no one requested this, so we don't have near-time plans related to this feature, neither a precise vision of possible API.

I'll create an issue for this - let's try to define the features you need more precisely and see when this can be done.

(Oct 11 '10 at 05:55) Alex Yakunin

Subscription:

Related questions