Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
imgharavi
Starting Member
5 Posts |
 Posted - 2011-06-08 : 02:03:28
|
| Hi,I have a full-text index enabled on my table and my table contains "MSSQLServer" but the following command does not return any row:select * from table1 where contains(*,'"*SQL*"')would you please help me how to solve this issue?I can not use like '%SQL%' because of performance issue. |
|
|
Lumbago
Norsk Yak Master
3271 Posts |
Posted - 2011-06-08 : 09:42:11
|
| From what I can understand your syntax is correct. Are you sure the column with the value "MSSQLServer" is indexed and that the index is updated?- LumbagoMy blog-> http://thefirstsql.com/2011/02/07/regular-expressions-advanced-string-matching-and-new-split-function-sql-server-2008-r2/ |
 |
|
|
Seventhnight
Master Smack Fu Yak Hacker
2878 Posts |
Posted - 2011-06-08 : 09:55:57
|
I'll admit that I don't have much experience with full-text indexes, BUT - from BOL, there is no mention of using 2 '*'.It is described only as finding words with the specified prefix:quote:
<prefix_term> Specifies a match of words or phrases beginning with the specified text. Enclose a prefix term in double quotation marks ("") and add an asterisk (*) before the ending quotation mark, so that all text starting with the simple term specified before the asterisk is matched. The clause should be specified this way: CONTAINS (column, '"text*"'). The asterisk matches zero, one, or more characters (of the root word or words in the word or phrase). If the text and asterisk are not delimited by double quotation marks, so the predicate reads CONTAINS (column, 'text*'), full-text search considers the asterisk as a character and searches for exact matches to text*. The full-text engine will not find words with the asterisk (*) character because word breakers typically ignore such characters.When <prefix_term> is a phrase, each word contained in the phrase is considered to be a separate prefix. Therefore, a query specifying a prefix term of "local wine*" matches any rows with the text of "local winery", "locally wined and dined", and so on. (this seems to actually look like "local* wine*"...)
I'm not sure that really helps, but I was curious.EDIT: Thinking about it, it doesn't really make sense to want to use a full-text index to search for the middle of a word... does it? How would an index be helpful? Generally, the beginning of a value would be most significant in it's positioning in an index... so skipping it would completely defeat the point. Right?Any of you hardcore gurus want to chime in and let me know I'm wrong?Corey I Has Returned!! |
|
|
|
imgharavi
Starting Member
5 Posts |
Posted - 2011-06-09 : 03:11:56
|
| thank you for your notes. I know that searching a part of a word makes word indexing and system has to scan all rows but it just need to scan words instead of all records. if full-text index could not support it, i have to return to use LIKE and it makes a my execution time long and indexing will not help me to speed up that search because my clients always want to search in any part of their field. |
|
|
|
Lumbago
Norsk Yak Master
3271 Posts |
Posted - 2011-06-09 : 03:24:56
|
| Ok, I'm seriously rusty on the fulltext indexing but have you tried to use FREETEXT istead of CONTAINS? ->select * from table1 where FREETEXT(*,'SQL')- LumbagoMy blog-> http://thefirstsql.com/2011/02/07/regular-expressions-advanced-string-matching-and-new-split-function-sql-server-2008-r2/ |
|
|
|
|
|
|
|
|
Full-text search a part of a word