May 16, 2006 at 6:33 am
I am using Full Text Search in SQL 2005.
I want to search for a partial word "rumple" in my table with the syntax:
"SELECT * FROM MyTable WHERE CONTAINS(MyTable.text, '"rumple*"')"
and in the results I see only words that starts with the word "rumple" like:
...my rumple example...
...my rumples example...
...my rumplers example...
etc...
But I don't get results like:
...my crumple example...
...my crumples example...
...my crumplers example...
I tried to add Asterix (*) before "rumple" like:
"SELECT * FROM MyTable WHERE CONTAINS(MyTable.TEXT, '"*rumple*"')"
But I still don't get the correct results!
How can I search like "LIKE '%rumple%'" with Full Text Search?
May 17, 2006 at 1:41 pm
I've created a full-text index on a table, and I have questions about SQL Server 2000's full-text search component. First, can full-text search look for a suffix (e.g., match the phrase *mation to determine whether a field contains words such as information)? Second, can you use full-text search to search for words in close proximity to each other? If you can, how many words can separate the search words before the search fails? Third, how can you modify the noise-word list?
The answer to your first question is that you can't directly do a suffix search. However, you can work around the limitation in a couple of ways. You can use the LIKE predicate in a clause such as WHERE mytext LIKE N'%suffix '. However, using LIKE requires a scan of all the rows in the table you're searching, which could impede performance. Alternatively, you can duplicate and index the data in reverse (e.g., imagination becomes noitanigami), then search for the string noitani*. . . .
This answer is from http://www.sqlmag.com/Article/ArticleID/38786/sql_server_38786.html
Hope this helps, though it's probably not the answer you were hoping for.
James
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply