I need help with the full text search. I want the full text to bring the data where all of the words are found in the table ideally more words should narrow the search result. however it is opposite. The more words i put in my search string it brings more records. So effectively it looks for the words in the column and if it finds any match it brings the record. Can you please help?
Here is my code
DECLARE @GlobalStringFraction NVARCHAR(100) = 'BIRKBECK HOUSE COLLIERS WAY NG86AT'
ensures that full text index is up to date before running the following
EXEC [Core].[USP_Core_WaitForFullTextIndexing] N'LocationFullTextCatalog';
SET @GlobalStringFraction = REPLACE(REPLACE(REPLACE(@GlobalStringFraction,
N' ', N'<>'), N'><', N''),
N'<>', N' ');
SET @GlobalStringFraction = N'"' + REPLACE(@GlobalStringFraction, N' ',
N'" AND "') + N'"';
SELECT LocationId ,
FROM [Spaces].[CrossContext].[LocationView] AS t
INNER JOIN FREETEXTTABLE([Spaces].[CrossContext].[LocationView], ConcatenatedAddressString, @GlobalStringFraction)
AS k ON t.LocationId = k.[key]
AND Iso3166MinusOneCountryId = 1
ORDER BY t.LocationId;
I have attached the sample data with the question.