MS SQL SERVER 2008 R2: Full-Text Search Problem

Question

Hi,

I am using sql express 2008 R2, I have problem in full-text search like this:

I have a table for books - Table1, and a NVARCHR type 'Title' field in it, one of book titles contains string like:  "  /1-39.1 ".

And I can't find the book with this title.

 

1)

When I use syntax like this,

SELECT * FROM Table1 WHERE CONTAINS(Table1.Title, '"/1-39.1*"')

------------

it selects nothing.

 

2) in this case (without asterisk),

SELECT * FROM Table1 WHERE CONTAINS(Table1.Title, '"/1-39.1"')

id      Title

1       book  /1-39.1

5       book  /39-59

2       book  /1-39

9       book  /24-39

it selects for me many rows too, that I don't want and I don't ask it to find:

 

What is the matter, what am I doing wrong?

I also tested it in ms sql server 2012, the result is the same.

Or else, May be it's a bug in ms sql?

Answer 1

Hi,

I am using sql express 2008 R2, I have problem in full-text search like this:

I have a table for books - Table1, and a NVARCHR type 'Title' field in it, one of book titles contains string like:  "  /1-39.1 ".

And I can't find the book with this title.

 1)

When I use syntax like this,

SELECT * FROM Table1 WHERE CONTAINS(Table1.Title, '"/1-39.1*"')

------------

it selects nothing.

 

2) in this case (without asterisk),

SELECT * FROM Table1 WHERE CONTAINS(Table1.Title, '"/1-39.1"')

id      Title

1       book  /1-39.1

5       book  /39-59

2       book  /1-39

9       book  /24-39

it selects for me many rows too, that I don't want and I don't ask it to find:

 

What is the matter, what am I doing wrong?

I also tested it in ms sql 2012, result is the same.

Answer 2

Hi,

because this forum is related to the TechNet wiki you should ask your question in the appropriate SQL Server forum for better help and support - http://social.technet.microsoft.com/Forums/en-US/category/sqlserver

---

Best Regards. When you see answers and helpful posts, please click Vote As Helpful, Propose As Answer, and/or Mark As Answer. This helps us build a healthy and positive community.

Answer 3

If you want a ‘LIKE’ style search, you need to use the T-SQL LIKE operator, rather than the full-text operator. Full-text does word-based search (with enhancements), rather than substring search.

 

Cheers, Bob

Answer 4

Hi MichaelMeliqyan,

As suggested above, you may have a try to use LIKE operation as “name like '/1-39.1%'”. Since there are noise words in ‘/1-39.1’ phrase, which you can view via:

SELECT * FROM sys.dm_fts_parser(N'/1-39.1', 1033, 0, 0) where special_term='Noise Word';

And that will lead to unexpected result.

---

Stephanie Lv

TechNet Community Support

Answer 5

Special characters are nomally break characters and are not indexed.    However, the other factor that made your second query so frustrating was the use of Stop Words.  since single letters and numbers are among the many things in the stop words.  The  code below is a test set of code for seeing how the full text engine breaks and searches the text. 

select * from sys.dm_fts_parser('"under_under dash-dash period.period comma,comma exclam!exclam at@at hash#hash crlf
crlf tab	tab dollar$dollar caret^caret and&and star*star lparen(lparen rparen)rparen plus+plus equal=equal
lcurl{lculr rcurl}rculr lbrack[lbrack rbrack]rbrack or|or lslash\lslash rslash/rslash colon:colon semic;semic 
quote''quote  dquote""dquote  less<less great>great quest?quest grave`grave tilde~tilde
1,2 3.4 5:6 7-8 9*10 11/12
1,a 3.b 5:c 7-d 9*e 11/f 
100$100 12''12 12""12 
34$56.00  $78.90 
/1-39.1 " ',
1033,--American English
NULL,--No stop words
0)--Accent Insensitive

You can turn off the stop words for your full text index.  If you do so, "1" will become searchable, et cetera. So, you could search for the following phrase using your code from above:

SELECT * FROM Table1 WHERE CONTAINS(Table1.Title, '"/1-39.1"')

 '"/1-38.1"'  With stop words are turned off and the phrase put together in double quotes should give you close to what you need.  It will look for  "1 38 1" and since the punctuation is ignored in this case, those just become logical spaces to the full text engine.

When you have noise words turned on, the '1's were thrown away and you were only searching on '39'.

Hope that helps.

RLF