The problem I am facing right now is that the full-text search in SQL doesn't yield the results that I would be expecting. The containstable method returns a result that does not contain the provided search term at all and I'm wondering why that is.
The term I am searching for is "tubus" which is input as "tubus%" into the query as seen below:
SELECT
k.[Rank],
si.Term
FROM CONTAINSTABLE(SearchIndex, Term, N'tubus%', LANGUAGE 1031) AS k
JOIN dbo.SearchIndex si ON k.[Key] = si.Id
LEFT JOIN dbo.Item i ON si.EntityId = i.Id AND si.EntityName = 'Item'
WHERE EntityName = 'Item'
The SearchIndex table is just a table that contains a column called Term which is a combined string of all words that should be included in the full-text search for the specified entity. An execution of the above query yields the following results:
| Rank | Term |
|---|---|
| 208 | CO2-Schlauch mit Tubus-Adapter (für Respironics 3473ADU-00) MA18566 WDT 26332 |
| 208 | Cuffill TrachealtubenDruckmesser LL MA17297 WDT 26271 |
| 208 | d-grip Tubus-/Narkoseschlauchhalter MA15253 WDT 92904 |
| 208 | iM3 Tubus-Fixierbänder aus Silikon, 50 Stück MA15389 WDT 28338 |
| 208 | Kruuse Tracheotomie Tubus für Pferde MA8825 WDT 94585 |
I've marked the wrong result in the table as bold and as you can see the word "tubus" doesn't appear in the Term at all.
The only thing I've tried so far was to use the correct language which in my case is LCID 1031 German but that didn't change the result at all. I've also looked around extensively but none of the problems or answers I've found so far could relate to my specific problem.
My only guess right now is that I might be misunderstanding the behaviour of the full-text search in SQL using the containstable method and that the results sometimes are "inaccurate" somehow. I am only a beginner in SQL so please bear with me but if someone knows why the previously described behaviour is occurring as it is please do tell! I would also love to know if the behaviour is as expected and why that is.