SQL CONTAINS function do not show records with the word 'under' even using u as keyword search

user_4468770 0
Viewed 488

I am having trouble showing records that contain the word "under" using the SQL CONTAINS function. I read the Microsoft documentation for this and based on example there using the AdventureWorks DB, result set is as expected but having a hard time when the same example is applied to search for records having the word "under".

enter image description here

Here is my script I working on that you can also execute to simulate the scenario:

--Creating table
CREATE TABLE MyTable
(
   ID INT NOT NULL IDENTITY(1,1),
   [Description] NVARCHAR(200) NULL
   CONSTRAINT PK_ID PRIMARY KEY CLUSTERED (ID)
)

--Populating table
INSERT INTO MyTable
SELECT 'Testing'
UNION SELECT 'This is it'
UNION SELECT 'Under the name of whoever'
UNION SELECT 'I undergo surgery'
UNION SELECT 'Under Armour'
UNION SELECT 'Frequent under the table'
UNION SELECT 'I am underpaid too'
UNION SELECT 'Anything under the sun'
UNION SELECT 'What is the matter peanut butter'
UNION SELECT 'Underline'
UNION SELECT 'Chainring Bolts'
UNION SELECT 'Chaining Nut'
UNION SELECT 'Chainring'
UNION SELECT 'Chain Stays'
UNION SELECT 'Chain'
UNION SELECT 'Dummy Chain'
UNION SELECT 'Land Down Under'
UNION SELECT 'Many Underlings'
UNION SELECT 'Only Undo'

--One time setup to enable fulltext search
EXEC sp_fulltext_database 'enable'
GO
CREATE FULLTEXT CATALOG FTCSearch
GO
CREATE FULLTEXT INDEX ON MyTable([Description])
   KEY INDEX PK_ID ON FTCSearch
GO

Query:

--Query showing phrases that have c prefix
SELECT * FROM MyTable
WHERE CONTAINS([Description], ' "c*" ')
--Getting the expected result

enter image description here

--Query showing phrases that have u prefix
SELECT * FROM MyTable
WHERE CONTAINS([Description], ' "under*" ')
--Not getting expected result: Missing phrases that contains 'under' word
--Missing: Under the name of whoever
--Missing: Under Armour
--Missing: Frequent under the table
--Missing: Anything under the sun
--Missing: Land Down Under
--Missing: Under the name of whoever

enter image description here

full-text-search tsql sql-server
2 Answers

I had the same issue. I did below query then it started working for me.

ALTER FULLTEXT INDEX ON MyTable SET STOPLIST = OFF

DECLARE @SearchWord varchar(200)='Under'

SELECT * FROM MyTable
WHERE CONTAINS(Description, @SearchWord);
user_121372280

Got an idea from the feedback of Roger Wolf and VBAGuy: 1. Create a custom stoplist based on the system/default 2. Remove the under word from the custom stoplist 3. Apply the created custom stoplist to Mytable

--1. Create custom stoplist based on system stopwords
CREATE FULLTEXT STOPLIST myStoplist FROM SYSTEM STOPLIST;

--2. Remove 'under' stopword in stoplist
ALTER FULLTEXT STOPLIST myStoplist DROP 'under' LANGUAGE 'English';
ALTER FULLTEXT STOPLIST myStoplist DROP 'under' LANGUAGE 'British English';
ALTER FULLTEXT STOPLIST myStoplist DROP 'under' LANGUAGE 'Simplified Chinese';
ALTER FULLTEXT STOPLIST myStoplist DROP 'under' LANGUAGE 'Thai';
ALTER FULLTEXT STOPLIST myStoplist DROP 'under' LANGUAGE 'Swedish';
ALTER FULLTEXT STOPLIST myStoplist DROP 'under' LANGUAGE 'Japanese';
ALTER FULLTEXT STOPLIST myStoplist DROP 'under' LANGUAGE 'Traditional Chinese';
ALTER FULLTEXT STOPLIST myStoplist DROP 'under' LANGUAGE 'Neutral';

--Check if successfully removed
SELECT * FROM sys.fulltext_stopwords WHERE stopword = 'under'

--3. Apply the custom stoplist to the table instead of the 'SYSTEM' as the default
ALTER FULLTEXT INDEX ON MyTable SET STOPLIST = myStoplist
user_44687700
Related