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'"';
PRINT @GlobalStringFraction;
SELECT LocationId ,
Rank ,
IsPoint
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;
"BIRKBECK" AND "HOUSE" AND "COLLIERS" AND "WAY" AND "NG86AT"
So its definitely an AND.DECLARE @GlobalStringFraction NVARCHAR(100) = 'BIRKBECK HOUSE COLLIERS WAY NG86AT' ,
@Wgs84Latitude FLOAT = 53.93022 ,
@Wgs84Longitude FLOAT = -2.0324707;
DECLARE @OriginPoint GEOGRAPHY;
SET @OriginPoint = Geography::STGeomFromText(N'POINT('
+ CAST(@Wgs84Longitude AS NVARCHAR(MAX))
+ N' '
+ CAST(@Wgs84Latitude AS NVARCHAR(MAX))
+ N')', 4326);
-- 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' ');
PRINT @GlobalStringFraction
SET @GlobalStringFraction = N'"' + REPLACE(@GlobalStringFraction, N' ',
N'" AND "') + N'"';
PRINT @GlobalStringFraction;
SELECT LocationId ,
Rank ,
IsPoint
FROM [Spaces].[CrossContext].[LocationView] AS t
INNER JOIN CONTAINSTABLE([Spaces].[CrossContext].[LocationView], ConcatenatedAddressString, @GlobalStringFraction)
AS k ON t.LocationId = k.[key]
AND Iso3166MinusOneCountryId = 1
ORDER BY t.LocationId;
Microsoft SQL Server 2014 - 12.0.4213.0 (X64)
Jun 9 2015 12:06:16
Copyright (c) Microsoft Corporation
Developer Edition (64-bit) on Windows NT 6.3 <X64> (Build 9600: ) (Hypervisor)
Microsoft SQL Server 2014 (SP2-CU1) (KB3178925) - 12.0.5511.0 (X64)
Aug 19 2016 14:32:30
Copyright (c) Microsoft Corporation
Developer Edition (64-bit) on Windows NT 6.3 <X64> (Build 9600: ) (Hypervisor)
WHERE ConcatenatedAddressString LIKE '%BIRKBECK%' AND ConcatenatedAddressString LIKE '%HOUSE%' AND ConcatenatedAddressString LIKE '%COLLIERS%'
AND ConcatenatedAddressString LIKE '%WAY%' AND ConcatenatedAddressString LIKE '%NG86AT%'
CONTAINSTABLE([Spaces].[CrossContext].[LocationView], ConcatenatedAddressString, '"BIRKBECK" AND "HOUSE" AND "COLLIERS"')
However as soon as i exceed the three words and put more to match it with where clause it does not return anything. INNER JOIN CONTAINSTABLE([Spaces].[CrossContext].[LocationView], ConcatenatedAddressString, '"BIRKBECK" AND "HOUSE" AND "COLLIERS" AND "Way"')
https://msdn.microsoft.com/en-us/library/ms142583(v=sql.120).aspx