I’m currently using full text search on 3 fields in my database, e.g. name, sku, details
However were having difficulty getting the search to work as expected e.g.
1) We’re searching for whole lines and not each word as say google for example e.g. Lets say we have a detail of “the cat sat on the mat” then searching for “cat" or “cat sat” will work but searching the “cat mat” won’t, so my question is, is there a way to have fts work smarter and break down the contents.
2) partial word search don’t work as per the example above if I search for “ma” nothing will get returned but if I search for “mat” it will, is there a way around this?
My current stored procedure can be found below, and can any replies ideally include how it should be amended please
Thanks in advance
DECLARE @FORMSOF as varchar(1000)
DECLARE @Phrase as varchar(1000)
SET @FORMSOF = 'FORMSOF (INFLECTIONAL, "' + @SearchText + '")'
SET @PHRASE = '"' + @SearchText + '"';
WITH Products AS
SELECT ID, [Name], Sku, Origin, Price, ROW_NUMBER() over (ORDER BY ID) AS 'RowNumber'
FROM mytable P
P.Active = 1
CONTAINS(Name, @FORMSOF) --Search for forms of input text in product name
OR CONTAINS(Detail, @FORMSOF) -- Search for forms of input text in product description
OR CONTAINS(Origin, @PHRASE) -- Search for input text in Origin field
OR SKU Like '%' + @SearchText + '%' -- Search for input text in Sku field
SELECT * FROM Products