ViaSat, a global provider of satellite and wireless communications, securely connects businesses, governments, and organizations to the Internet. Learn how ViaSat’s Network Solutions Engineer, drove the transition from a traditional network support to a DevOps-centric model.
Course Of The Month
5 days, 2 hours left to enrollBecome a Premium Member and unlock a new, free course in leading technologies each month.
Solved
SQL Server 2014 Full Text Search Returns more results with narrow search
Posted on 2016-09-13
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
I have attached the sample data with the question.
regards
SampleData.xlsx
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;
I have attached the sample data with the question.
regards
SampleData.xlsx
[X]
Welcome to Experts Exchange
Add your voice to the tech community where 5M+ people just like you are talking about what matters.
- Help others & share knowledge
- Earn cash & points
- Learn & ask questions
-
10
7
17 Comments
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
https://msdn.microsoft.com/en-us/library/ms142583(v=sql.120).aspx
Active 1 day ago
Thanks for your reply. Well the string @GlobalStringFraction after formatting gives the following value
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,
"BIRKBECK" AND "HOUSE" AND "COLLIERS" AND "WAY" AND "NG86AT"
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,
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
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
Active 1 day ago
Thanks again but i am already using Freetexttable. containable also doesn not return the result.
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)?
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)?
Can you try the actual two statements below:
SELECT LocationId ,
Rank ,
IsPoint
FROM [Spaces].[CrossContext].[L
INNER JOIN FREETEXTTABLE([Spaces].[Cr
AS k ON t.LocationId = k.[key]
AND Iso3166MinusOneCountryId = 1
ORDER BY t.LocationId;
SELECT d.LocationId ,
f.[Rank] ,
d.IsPoint
FROM CONTAINSTABLE( [Spaces].[CrossContext].[L
INNER JOIN [Spaces].[CrossContext].[L
ON f.[key]=d.LocationId
AND d.Iso3166MinusOneCountryId
--ORDER BY f.RANK
ORDER BY t.LocationId;
Maybe is just the double quotes buggering your query returned result set.
SELECT LocationId ,
Rank ,
IsPoint
FROM [Spaces].[CrossContext].[L
INNER JOIN FREETEXTTABLE([Spaces].[Cr
AS k ON t.LocationId = k.[key]
AND Iso3166MinusOneCountryId = 1
ORDER BY t.LocationId;
SELECT d.LocationId ,
f.[Rank] ,
d.IsPoint
FROM CONTAINSTABLE( [Spaces].[CrossContext].[L
INNER JOIN [Spaces].[CrossContext].[L
ON f.[key]=d.LocationId
AND d.Iso3166MinusOneCountryId
--ORDER BY f.RANK
ORDER BY t.LocationId;
Maybe is just the double quotes buggering your query returned result set.
Active 1 day ago
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;
Active 1 day ago
Actually i already tried directly without double quotes before posting and it did not work. Same result.
Sorry i am being pain
Sorry i am being pain
No way...that's why were here to help(and learn at the same time).
Also, did you run my queries? not dynamic sql just the ones I posted?
Can you please run statement below and post back results?
select @@version;
Also, did you run my queries? not dynamic sql just the ones I posted?
Can you please run statement below and post back results?
select @@version;
Active 1 day ago
I really appreciate your time and effort.
Here is the result of @@version
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)
Active 1 day ago
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
Yes I got that and thinking about what may have cause the CONTAINSTABLE to not return anything. Interesting that the version you are running 12.0.4213.0 is not listed under "known" SQL 2014 build versions...
https://support.microsoft.com/en-ca/kb/2936603
found your version here... http://www.sqlservercentral.com/articles/SQL+Server+2014/101274/ related to fix " Partial results in a query of a clustered columnstore index in SQL Server 2014" https://support.microsoft.com/en-us/kb/3067257
https://support.microsoft.com/en-ca/kb/2936603
found your version here... http://www.sqlservercentral.com/articles/SQL+Server+2014/101274/ related to fix " Partial results in a query of a clustered columnstore index in SQL Server 2014" https://support.microsoft.com/en-us/kb/3067257
Active 1 day ago
Hi lcohan,
Thanks a lot. we have now upgraded to the SP2 and CU and now the version of the server is
However still CONTAINSTABLE is not returning anything.
regards,
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)
However still CONTAINSTABLE is not returning anything.
regards,
Active 1 day ago
Just found out another weird thing. I have also tested with the normal where clause
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
Does CONTAINTABLE has the limit to look for max of three words?
WHERE ConcatenatedAddressString LIKE '%BIRKBECK%' AND ConcatenatedAddressString LIKE '%HOUSE%' AND ConcatenatedAddressString LIKE '%COLLIERS%'
AND ConcatenatedAddressString LIKE '%WAY%' AND ConcatenatedAddressString LIKE '%NG86AT%'
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"')
INNER JOIN CONTAINSTABLE([Spaces].[CrossContext].[LocationView], ConcatenatedAddressString, '"BIRKBECK" AND "HOUSE" AND "COLLIERS" AND "Way"')
Does CONTAINTABLE has the limit to look for max of three words?
Active 1 day ago
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
Thanks a lot for all of your time and effort
regards,
Ali
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
http://mssqltipsandtricks.blogspot.ca/2012/07/noisestop-words-in-sql-server.html
https://msdn.microsoft.com/en-us/library/ms142551.aspx
Featured Post
Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.
Question has a verified solution.
If you are experiencing a similar issue, please ask a related question
Suggested Solutions
| Title | # Comments | Views | Activity |
|---|---|---|---|
| Convert results from the query output to insert into sql table | 3 | 29 | |
| Force application to connect using sql native client for mirroring | 3 | 19 | |
| Load Fact table in SQL Server SSIS package | 14 | 45 | |
| t-sql left join | 2 | 34 |
This article explains how to reset the password of the sa account on a Microsoft SQL Server. The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied?
You can use SQL Server Initialize from Backup…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables.
Make a table:
Update a specific column given a specific row using the UPDATE statement:
Remove a set of values using the DELETE s…
Suggested Courses
Course of the Month5 days, 2 hours left to enroll
738 members asked questions and received personalized solutions in the past 7 days.
Join the community of 500,000 technology professionals and ask your questions.