CODEWITHSUNDEEP
sql-servert-sqlfull-text-search
nam vo
nam vo

Reputation: 3437

CONTAINS cannot find the item that LIKE does

I have a table Player that has player with name Emre Can. However, I cannot get it using contains like below:

SELECT  [Id], [Name]      
FROM [Player]
where contains(name,'"*Can*"')  -- show 10 results without emre can

José Cañas, Lorik Cana, ... but no Emre Can

However, if I use 

SELECT  [Id],[Name]      
FROM [Player]
where name like '%Can%'  -- work fine??

where contains(name,'"*Emre*"')  -- also works fine

UPDATE: It seems Can is listed on the stop lists.

ALTER FULLTEXT INDEX ON Player SET STOPLIST = OFF -- works now

Upvotes: 0

Views: 86

Answers (1)

jean
jean

Reputation: 4350

Contains can be used to search a prefix, not a suffix and ill not work like the "LIKE" string operator. Please refer to the documentation

Edit

After a search found this not recommended hack you can use to allow sufix search

Add a column in your table which stores the reverse of the string like

SET NewColumnName = REVERSE(ColumnName)

CREATE PROCEDURE sps_searchSuffix(@searchString varchar(8000)) AS

SET @searchString = REVERSE(@searchString)

DECLARE @Q nVARCHAR(MAX)
SET @Q = 'SELECT * FROM TableName WHERE CONTAINS (ColumnName,''"'+@searchString+'*"'''+')'
EXEC SP_EXECUTESQL @Q

And call it like this if you want to search "garding" and you have a data like "regarding"

DECLARE @ST VARCHAR(500)
SET @ST = 'garding'
PRINT @ST
EXEC sps_searchSuffix @ST

found here

Upvotes: 1

Related Questions