SQL Server Full-Text Search returns unexpected result

Question

I have a couple of million records in a Microsoft SQL Server database table. The search with [Column] LIKE '%test%' is way too slow. Therefore, I use a full text search.

My query looks as follows after a lot of tries with CONTAINS and FREETEXT...

SELECT [SearchText] FROM [Service].[CatalogArticleCache] AS t
INNER JOIN CONTAINSTABLE([Service].[CatalogArticleCache], [SearchText], '"*426*"') AS s1 ON t.ArticleId = s1.[KEY]
INNER JOIN CONTAINSTABLE([Service].[CatalogArticleCache], [SearchText], '"*211*"') AS s2 ON t.ArticleId = s2.[KEY]
INNER JOIN CONTAINSTABLE([Service].[CatalogArticleCache], [SearchText], '"*Geschweisst*"') AS s3 ON t.ArticleId = s3.[KEY]
INNER JOIN CONTAINSTABLE([Service].[CatalogArticleCache], [SearchText], '"*rohr*"') AS s4 ON t.ArticleId = s4.[KEY]
--INNER JOIN CONTAINSTABLE([Service].[CatalogArticleCache], [SearchText], '"*bogen*"') AS s5 ON t.ArticleId = s5.[KEY]
ORDER BY [SearchText]

That query returns a couple of records such as...

426 211357 .Geschweisste Rohrbogen 2d90
426 211682 .Geschweisste Leitungsrohre ungegl.
426 211990 .Geschweisste Rohrbogen D + 100
...

As soon as I include the second last line of my SQL query, I expect the result in the middle would be gone. Unfortunately, the query returns not a single row.

Why that and how to fix it?

Answer 1

The issue is that in MS SQL Server leading wildcards are not supported, you can only search for prefixes with wildcards, like "term*". According to my understanding all first 4 JOINs even though with leading wildcards in search terms get resolved because the underlying text contains words which start with your 426, 211, Geschweisst and rohr terms. Leading dot in .Geschweisste gets ignored because it is a stop word.

But there is no word in your text which starts with bogen because bogen in Rohrbogen is located at the end of the word. Since you are using INNER JOIN you get no results at all.

Try to reconstruct your query to only use wildcards with prefixes.

Also this query may be helpful when investigating how FTS engine actually parses your search terms:

select * from sys.dm_fts_parser('"search terms"', 1033, null, 0)

Hope this helps!