SQLTeam.com | Weblogs | Forums

Full Text search:Contains isn’t returning all results


#1

I have following table
create table my_tst
(my_tst int not null Identity(1,1),
project int not null,
my_NO varchar(60) null,
my_NAME varchar(40) null,
constraint PK_my_tst PRIMARY KEY nonclustered
(my_tst)
)
IF not EXISTS(SELECT 1 FROM sys.fulltext_catalogs WHERE name = 'FTC_FTSearch')
CREATE FULLTEXT CATALOG [FTC_FTSearch] WITH ACCENT_SENSITIVITY = OFF
AS DEFAULT

GO

CREATE FULLTEXT INDEX ON my_tst ( my_NO, my_NAME)
KEY INDEX PK_my_tst
on (FTC_FTSearch ,FILEGROUP INDEX_GROUP)
go
insert into my_tst
(project,my_NO ,my_NAME)
values (4, 'P-51410', 'entry 1 sss'),
(4, 'P-14120A', 'entry 2 sss'),
(4, 'PE-3497-141', 'entry 3 sss')

select * from my_tst where project = 4 and my_no like '%141%'
select * from my_tst where project = 4 and CONTAINS(my_no, '"141"')

returns different result
tests were done in SQL Server 2008 R2 and SQL Server 2014.

Can you please advise how can I fix it?
I disabled the STOPLIST, rebuild the FULTEXT index
ALTER FULLTEXT INDEX ON my_tst SET STOPLIST = OFF
And
This
sp_configure 'show advanced options', 1;
RECONFIGURE;
GO
sp_configure 'transform noise words', 1;
RECONFIGURE;
GO
Still the same results