0

(This a follow up to this question)

I have a table with customers. The table has one column Name that has a full text index. One of the customers has the surname 'Said' and another has the surname 'Saiid'.

Name
---------
Joe Said
Joe Saiid

When I query for 'Said' using the CONTAINS predicate nothing is returned. But querying for 'Saiid' using CONTAINS returns the record.

Here is a demo, using the suggestion from this answer

CREATE TABLE T_NOM
(NOM_ID INT IDENTITY CONSTRAINT PK_NOM PRIMARY KEY, 
NOM_NOM VARCHAR(32))

INSERT INTO T_NOM VALUES 
('Joe'), 
('Joe Doe'), 
('Joe Bar'), 
('Foo Joe'), 
('Alice Bob'), 
('Charles David'), 
('Elizabeth Fred'),
('Joe Said'), 
('Joe Saiid')
GO

CREATE FULLTEXT CATALOG FT;
GO

CREATE FULLTEXT INDEX ON T_NOM (NOM_NOM) KEY INDEX PK_NOM ON FT;
GO

Again, this returns nothing

SELECT * FROM T_NOM WHERE CONTAINS(NOM_NOM,'Saiid');

These 2 queries return the record for Joe Said and Joe Doe respectively.

SELECT * FROM T_NOM WHERE CONTAINS(NOM_NOM,'Said');
SELECT * FROM T_NOM WHERE CONTAINS(NOM_NOM,'Doe');
niborag
  • 1
  • 1
  • 1
    Probably because it is a stop word. You need to read the documentation on how full text indexes work quite carefully. – Gordon Linoff Jan 06 '21 at 17:55
  • Hi Gordon, thanks for the tip. In the database there is no Storage -> Full Text Stoplists ... where else could stop lists be stored? – niborag Jan 06 '21 at 17:57
  • Hi @Charlieface, yes that answers my question. Something with stop words. If I switch off the stop list for that table / full index, the query works. – niborag Jan 06 '21 at 18:03
  • You can create your own list of stopwords and also this list can be fill with the default one in which you will remove the 'said' word... – SQLpro Jan 07 '21 at 09:30

0 Answers0