Search "Our" Use Full Text Search with Contains in SQL Server

Question

We Use Full text search and Contains to search between records in SQL Server 2008 R2, here are the samples:

NEWS(Title): "We", "New", "Our", "Long-Term", "Seem", "Non.Active"

So as you see in the News table the title field have the values. We can search all of the values except "Long-Term" And "Non.Active", actually we can not search the words includes dash("-") or dot("."). We also check these tips:

SELECT * FROM NEWS WHERE  Contains(Title, 'Non.Active');
SELECT * FROM NEWS WHERE  Contains(Title, 'Non Active');
SELECT * FROM NEWS WHERE  Contains(Title, 'NonActive');
SELECT * FROM NEWS WHERE  Contains(Title, 'Non*');
SELECT * FROM NEWS WHERE  Contains(Title, 'Active');
SELECT * FROM NEWS WHERE  Contains(Title, 'Non');
SELECT * FROM NEWS WHERE  Contains(Title, '*Active');
SELECT * FROM NEWS WHERE  Contains(Title, ' "Non.Active" ');
SELECT * FROM NEWS WHERE  Contains(Title, ' "Non Active" ');
SELECT * FROM NEWS WHERE  Contains(Title, ' "NonActive" ');
SELECT * FROM NEWS WHERE  Contains(Title, ' "Non*" ');
SELECT * FROM NEWS WHERE  Contains(Title, ' "*Active" ');
SELECT * FROM NEWS WHERE  Contains(Title, ' "Active" ');
SELECT * FROM NEWS WHERE  Contains(Title, ' "Non" ');

But none of them return any result. Also we rebuild Full Text Index and yet we did not get any result.

So the question is: Is there any way to search the words include "." or "-" with full text Contains predicate? any suggestion

UPDATE

I'm really sorry the main problem is another?

you all right about two words of "non" and "Action". but the main case I test it is "We.Our" and steel not return any result? That's so wired, I test "Non.Action" with above search and worked but "We.Our" don't. So I try another record, I inserted the "our" and the search result is yet null. The problem is about "Our" word? what is the problem with "our" I also check it in SQL Server 2012, and not worked also. is there any one have any idea about this?

possible duplicate of [SQL Server Full Text Search Escape Characters?](http://stackoverflow.com/questions/995478/sql-server-full-text-search-escape-characters) — CodingIntrigue, Oct 07 '13 at 07:21
@RGraham The Answer you mentioned Not worked for me. The samples contains "{" or """ returned result in my search also. but how about "." or "-"? — Saeid Alizade, Oct 07 '13 at 07:31
The point of that answer is that SQL ignores all special characters and treats this as two words: *non* and *active*. — CodingIntrigue, Oct 07 '13 at 07:33
@RGraham if your answer is correct, the `SELECT * FROM NEWS WHERE Contains(Title, ' "Non" ');` Or `SELECT * FROM NEWS WHERE Contains(Title, 'Non');` must return value, but not. — Saeid Alizade, Oct 07 '13 at 07:44

score 0 · Answer 1 · edited Jan 06 '16 at 17:03

0

In My cases, it simply works as

SELECT * FROM NEWS WHERE  Contains(Title,'"Non.*"');

But i hear there is little bug in 2008 version.May the problem will solve after updating sql server instances.

edited Jan 06 '16 at 17:03

Keith

20,636
11
84
125

answered Oct 07 '13 at 07:52

Regon

392
1
4
17

Sorry for asking, Have u used "our" keyword as a work breaker or not ? – Regon Oct 07 '13 at 08:06

score 0 · Answer 2 · answered Oct 07 '13 at 08:15

0

You can check how needed data is stored in Fulltext catalog:

SELECT *
FROM sys.dm_fts_index_keywords_by_document(db_id('DBname'), object_id('TableName'))
WHERE document_id = <Unique Id>

What about the "our" word, it seems to be a stop-word.

answered Oct 07 '13 at 08:15

GriGrim

2,891
1
19
33

score 0 · Answer 3 · answered Oct 23 '13 at 05:07

0

The point is the Block words and Noisy words should be checked, in my case the our was a noisy word, I don't know why. but the problem solved

answered Oct 23 '13 at 05:07

Saeid Alizade

853
3
9
18

Search "Our" Use Full Text Search with Contains in SQL Server

3 Answers3