Search with CONTAINS in SQL Server

Question

I have created a student table and has the following records.

enter image description here

I want to perform search on the same table. I tried with the following query.

SELECT * FROM Students WHERE Name LIKE '%AM%'

It returns First two rows as resultset, which is correct.

I would like to perform the same query using the keyword CONTAINS, instead of LIKE.

I tried the below query, but its not returning any resultset.

SELECT * FROM Students WHERE CONTAINS(Name,'"AM"')
SELECT * FROM Students WHERE CONTAINS(Name,'"AM*"')
SELECT * FROM Students WHERE CONTAINS(Name,'"*AM*"')

Kindly help me to resolve this.

Thanks in advance.

Is the column in a [full text index](http://technet.microsoft.com/en-us/library/ms187317.aspx)? Apart from that it's `'AM'` instead of `'"AM"'`. — Tim Schmelter, Apr 30 '14 at 11:32
Read the documentation for `contains` (http://technet.microsoft.com/en-us/library/ms187787.aspx). The second paragraph gives a bulleted list of what `contains` can search for. Patterns in the middle of a word is not in the list. — Gordon Linoff, Apr 30 '14 at 11:37
Hi Tim, Appreciate your reply. Can you please tell me, How can I create a full text index on this table? Thanks — Sanoop, Apr 30 '14 at 11:41
[how to create fulltext index](http://msdn.microsoft.com/en-us/library/ms189520.aspx) — Nagaraj S, Apr 30 '14 at 11:43
http://stackoverflow.com/questions/588025/sql-problem-using-contains-doesnt-work-but-like-works-fine — Nagaraj S, Apr 30 '14 at 11:47
I have tried following code to create fulltext index. But still it doesn't work with CONTAINS. 'GO CREATE UNIQUE INDEX ui_ukJobCand2 ON Student(Name); CREATE FULLTEXT CATALOG ft2 AS DEFAULT; CREATE FULLTEXT INDEX ON Student(Name) KEY INDEX ui_ukJobCand2 WITH STOPLIST = SYSTEM; GO' — Sanoop, Apr 30 '14 at 12:02
@NagarajS : Fulltext index created successfully. But the query is not returning any result. — Sanoop, Apr 30 '14 at 12:08
@NagarajS : ALTER Query executed successfully. But still CONTAINS not returning any resultset. — Sanoop, Apr 30 '14 at 12:14
@NagarajS : This works for beginning string. If I use middle characters, its not returning the result. — Sanoop, May 06 '14 at 07:08
As per my understanding CONTAINS doesn't work for middle characters. — Sanoop, May 06 '14 at 07:36

score 0 · Answer 1 · edited May 23 '17 at 12:03

0

Define Full-Text Index for your table column and execute your query.

sample to add Full-Text Index : Cannot use a CONTAINS or FREETEXT predicate on table or indexed view because it is not full-text indexed

edited May 23 '17 at 12:03

Community

1
1

answered Apr 30 '14 at 12:20

Aje707707

13
2

How can I search for middle characters? – Sanoop May 06 '14 at 07:36

Alex Peshik · Answer 2 · 2014-05-06T09:49:13.703

Start MSSQLFDLauncher (SQL Server -> Config Tools -> Config Manager -> Full-text Filter Daemon Launcher -> Start)
Run this code (or something like this with your necessary index)

CREATE UNIQUE INDEX IX_ID ON Students(ID)

CREATE FULLTEXT CATALOG Students_Catalog
You can't use full-text search with the middle of the word, using CTE is not correct too (because of full-text index), so we'll try to use some tricks...
Add function that split names and make each letter like a word

/* Author: Paul Simpson (modified by Alex Peshik for test purposes!) Create date: 7/19/2012 (modified 5/6/2014) Description: Puts spaces+underscores between letters. Example: "GEORGE" becomes "_G _E _O _R _G _E" */

CREATE FUNCTION udf_PutSpacesBetweenChars

(@String VARCHAR(100))

RETURNS VARCHAR(100)
AS
BEGIN
   DECLARE @pos INT, @result VARCHAR(100); 
   SET @result = @String; 
   SET @pos = 2 -- location where we want first space 
   WHILE @pos < LEN(@result)+1 
   BEGIN 
       SET @result = STUFF(@result, @pos, 0, SPACE(1));
       SET @result = STUFF(@result, @pos+1, 0, '_') -- add underscore (to imitate word)
       SET @pos = @pos+3; -- (the original was +2)
   END 
   RETURN '_'+@result; -- add leading underscore (the original is w/o it)
END

Add new column and index on it (we can't use persisted computed column as far as it's non-deterministic column).

ALTER TABLE Students ADD NameSpaced varchar(100)

UPDATE Students SET NameSpaced=dbo.udf_PutSpacesBetweenChars(Name)

CREATE FULLTEXT INDEX ON Students (NameSpaced) KEY INDEX IX_ID ON Students_Catalog;
Use special technique to search near letters/'words'. Distance between words is 1, TRUE means only that order ("a,m" and not "m,a").

SELECT * from Students WHERE CONTAINS(NameSpaced,' NEAR((_a,_m),1,TRUE) ')

Sorry that solution is quite huge, I have no ideas how to simplify it...

Search with CONTAINS in SQL Server

2 Answers2