CODEWITHSUNDEEP
sql-server
El Mac
El Mac

Reputation: 3418

SQL Server Full-Text-Index Search does not work

The full-text-index searches are not returning the expected results. The index is built by now, but not finding some really basic strings.

This query does not return the user called "Other 11".

SELECT KEY_TBL.RANK, FT_TBL.Firstname
FROM [user].[User] AS FT_TBL
         INNER JOIN
     CONTAINSTABLE([user].[User], Firstname,
                   '"Other*"') AS KEY_TBL
     ON FT_TBL.UserId = KEY_TBL.[KEY]
ORDER BY KEY_TBL.RANK DESC
GO

If I rewrite the query like this, it will work and return the user called "Other 11".

SELECT KEY_TBL.RANK, FT_TBL.Firstname
FROM [user].[User] AS FT_TBL
         INNER JOIN
     CONTAINSTABLE([user].[User], Firstname,
                   '"Other 11"') AS KEY_TBL
     ON FT_TBL.UserId = KEY_TBL.[KEY]
ORDER BY KEY_TBL.RANK DESC
GO

Something similar happens with FREETEXTTABLE and CONTAINS with a predicate search is also not working.

What could be the reason for this behavior?

Upvotes: 0

Views: 53

Answers (1)

SQLpro
SQLpro

Reputation: 5177

Other and 11 are two differents words.

With a FTS predicate like '"Other*"' you will find "Otherwise" but never "Other 11"

You need to use a NEAR predicate like :

'NEAR((Other, 11),1, TRUE)'

Upvotes: 1

Related Questions