SQL Server 2005 supports two forms of full-text search, FREETEXT and CONTAINS. CONTAINS is used to search for rows that contain words, phrases, partial phrases, words with the same stem, proximity searches, synonyms (using a thesaurus lookup), and more.
Here is a simple example:
SELECT note_id, note_text
FROM productnotes
WHERE CONTAINS(note_text, 'handsaw');
WHERE CONTAINS(note_text, 'handsaw') means find the word handsaw in column note_text.
CONTAINS also supports the use of wildcards:
SELECT note_id, note_text
FROM productnotes
WHERE CONTAINS(note_text, '"anvil*"');
'"anvil*"' means match any word that starts with anvil. Note that unlike LIKE, full-text searching uses * as the wildcard character (instead of %). Wildcards may be used at the beginning or end of a string. Also, when passing simple text to CONTAINS then that text is enclosed within single quotes. When passing wildcards each search phrase must be enclosed within double quotes inside those outer single quotes. Failing to do this will likely cause your searches to return no matches.
CONTAINS also supports Boolean operators AND, OR, and NOT. Here are a couple of examples:
SELECT note_id, note_text
FROM productnotes
WHERE CONTAINS(note_text, 'safe AND handsaw');
SELECT note_id, note_text
FROM productnotes
WHERE CONTAINS(note_text, 'rabbit AND NOT food');
When searching through extremely long text there is a greater likelihood of matches being found if search terms are near each other in the saved data. A simple AND search matches terms anywhere in the text, but NEAR can be used to instruct the full-text search engine to only match terms when they are close together. Here is an example:
SELECT note_id, note_text
FROM productnotes
WHERE CONTAINS(note_text, 'detonate NEAR quickly');
'detonate NEAR quickly' means match only rows that contain the words detonate and quickly near each other.
Sometimes you may want to match a word that is part of the same family (based on the same stem). For example, if you were searching for "life" you'd also want to match "lives". Obviously, a wildcard of life* could not help here, and using li* would likely match too many false positives. This is where inflectional matching helps. Here is an example:
SELECT note_id, note_text
FROM productnotes
WHERE CONTAINS(note_text, 'FORMSOF(INFLECTIONAL, life)');
'FORMSOF(INFLECTIONAL, life)' instructs the full-text engine to look for any words that share the same stem as the specified word, in this case "life".
FORMSOF() also supports THESAURUS searches, where words can match synonyms. To use this functionality you must first populate an XML thesaurus file with words and their synonyms.
Verity can index database querys aswell. And what about documents (word, pdf, html etc.) in SQL Freetext ?
-- Mikkel, from Denmark
The benefits are no limitation on document count, no external collections to have to manage, searching can be used by any database client (not just CF), it does indeed index Excel and Word and other formats, being able to combine WHERE clauses that perform full-text search and other filters is very useful.
The downsides are that Verity is more capable, SQL Server full-text can't do the 'did you mean' type search, it has a much less powerful query language, as an example.
--- Ben
What about performance - Verity vs. SQL?
I guess that Verity is also better at showing summary, highlighting, categories.
--- Ben
--- Ben
This feels new! If it can easily index Excel and Word, AND pdf then I'm really excited about this. Verity has lovely feature sets, but (and this easily can be a product of my addled brain) I've always had issues with each use of the Verity engine over something: indexing, corruption, ease of use. Again, I accept that I've got the low-gear brain on Verity, but using SQL Server alone would make me more happy. Do you have any nice handy links on indexing external documents with 2005 SQL?
Yes, this has always been the case, ever since SQL FTS shipped with SQL Server 7.0. This feature and many others related to SQL Server Full Text Search (FTS) have been greatly imporved in SQL Server 2005 relative to past version of SQL Server, including vastly improved FT Indexing performance. I've worked with SQL FTS ever since it was first incorporated in SQL 7.0 in 1998 and have spoke at several conferences on this and related topics. I cannot speak about SQL FTS performance relative to Verity, but 10,000 rows is a trival table size and can be FT Indexed with SQL 2005 in minutes and no DBA is necessary. I also maintain a blog on this topic at http://jtkane.spaces.live.com/ and porting all of the technical content over to http://www.SQLFTS.com this month with a more formal lauch in Feb. 2007.
Thanks,
John
WHERE CONTAINS(note_text, #form.search# AND #form.search');
If i use "select TextSearch from CLGHistory where contains(TextSearch,'"1ALL*"') " then it will disply me the records.
but if i use "select TextSearch from CLGHistory where contains(TextSearch,'"*1ALL*"') " it will not display me any results
Pls help me