Avatar of Ali Shah
Ali Shah
Flag for United Kingdom of Great Britain and Northern Ireland asked on

SQL Server 2014 Full Text Search Returns more results with narrow search

Hi Guys,

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'"';

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;

Open in new window


I have attached the sample data with the question.

regards
SampleData.xlsx
Microsoft SQL ServerMicrosoft SQL Server 2008

Avatar of undefined
Last Comment
Ali Shah

8/22/2022 - Mon
lcohan

I think it depends how the CONTAINS/FREETEXT search SQL code is written or in other words if OR is used instead of AND so the result set widens not narrows the search in that case. Also RANK can be used to add/remove rows to the returned record set and for more details please see:
https://msdn.microsoft.com/en-us/library/ms142583(v=sql.120).aspx
Ali Shah

ASKER
Thanks for your reply. Well the string @GlobalStringFraction after formatting gives the following value
"BIRKBECK" AND "HOUSE" AND "COLLIERS" AND "WAY" AND "NG86AT"

Open in new window

So its definitely an AND.
Also i need the Rank column and if use the Contains function i can't have a Rank column. I already did a bit of reading on the link you sent but it did not help.

regards,
lcohan

I think you can actually have a RANK in contains and the difference between the two may be the answer you're looking for because:

CONTAINSTABLE - http://msdn.microsoft.com/en-us/library/ms189760(v=SQL.90).aspx 
<<Returns a table of zero, one, or more rows for those columns containing character-based data types for precise or fuzzy (less precise) matches to single words and phrases, the proximity of words within a certain distance of one another, or weighted matches>> - or if my understanding is correct that means more precise search in other words

VS.

FREETEXTTABLE - http://msdn.microsoft.com/en-us/library/ms177652(v=SQL.90).aspx 
<<Returns a table of zero, one, or more rows for those columns containing character-based data types for values that match the meaning, but not the exact wording, of the text in the specified freetext_string. >> - or less "accurate" search meaning more returns in the result set.

Please see more details and better explanation about the differences here:

https://social.msdn.microsoft.com/Forums/sqlserver/en-US/9e1095e5-2db1-484c-a23c-04c70872087a/difference-between-freetexttable-and-containstable?forum=transactsql


Limit/use CONTAINSTABLE with RANKing here:

https://msdn.microsoft.com/en-us/library/cc879245.aspx
Experts Exchange has (a) saved my job multiple times, (b) saved me hours, days, and even weeks of work, and often (c) makes me look like a superhero! This place is MAGIC!
Walt Forbes
Ali Shah

ASKER
Thanks again but i am already using Freetexttable. containable also doesn not return the result.
lcohan

Well I suggested the oposite - use CONTAINSTABLE not FREETEXTTABLE.
What do you mean by "containable also doesn not return the result."?
Does not return any results OR it returns exactly the same number as FREETEXTTABLE(which is very unlikely)?
ASKER CERTIFIED SOLUTION
lcohan

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
Ali Shah

ASKER
Here is my code with Containstable and it does not return anything
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; 

Open in new window

⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
Ali Shah

ASKER
Actually i already tried directly without double quotes before posting and it did not work. Same result.
Sorry i am being pain
SOLUTION
lcohan

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
Ali Shah

ASKER
I really appreciate your time and effort.
Here is the result of @@version
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)

Open in new window

Ali Shah

ASKER
Sorry but did you pick my answer that i did try your queries and they produce the same results with FreeTextTable all results and with Containstable no result at all
Your help has saved me hundreds of hours of internet surfing.
fblack61
SOLUTION
lcohan

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
Ali Shah

ASKER
Hi lcohan,

Thanks a lot. we have now upgraded to the SP2 and CU and now the version of the server is
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)

Open in new window


However still CONTAINSTABLE is not returning anything.

regards,
Ali Shah

ASKER
Just found out another weird thing. I have also tested with the normal where clause
WHERE  ConcatenatedAddressString LIKE '%BIRKBECK%' AND  ConcatenatedAddressString LIKE '%HOUSE%' AND  ConcatenatedAddressString LIKE '%COLLIERS%'
  AND  ConcatenatedAddressString LIKE '%WAY%' AND  ConcatenatedAddressString LIKE '%NG86AT%'

Open in new window



It comes with the result as expected. However in my CONTAINSTABLE i have tested by putting direct string and until three words it brings back the results same as where clause
CONTAINSTABLE([Spaces].[CrossContext].[LocationView], ConcatenatedAddressString, '"BIRKBECK" AND "HOUSE" AND "COLLIERS"')

Open in new window

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"')

Open in new window


Does CONTAINTABLE has the limit to look for max of three words?
Ali Shah

ASKER
Well, Well, Well for some reasons the word "WAY" causes all the issues, I have removed the word "WAY" from the search string and it works.
Thanks a lot for all of your time and effort

regards,

Ali
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
lcohan

OMG...I am sooo bad...how come I didn't notice it and think to check that...I had one theought about noise words but that slipped away unfortunately and please see here the list with all of them and more about noise/stopwords as well. You could find/edit that list BTW and add/remove any words you want to search or not - I mena if for instance you remove "WAY" from the noise word list the search should work on it however....that may cause just too much "noise"

http://mssqltipsandtricks.blogspot.ca/2012/07/noisestop-words-in-sql-server.html

https://msdn.microsoft.com/en-us/library/ms142551.aspx
Ali Shah

ASKER
Thanks again for your help and support. Its been really a great learning curve for me on the Full Text Search