Index Server Faq
CONTAINS will return exact or literal matches to the search phrase in its bare form. For instance, queries on the word run will only match on rows containing the word run and not on runs, or runt. Rows are returned only when you have character‑by‑character matches. However, you can select the degree of imprecision, closeness, or fuzziness in your search. Taken to its extreme, you can make CONTAINS the functional equivalent of FREETEXT.
Select * from table_name where CONTAINS(column_name, 'SearchPhrase').
Keep in mind that you should never do an unqualified column selection like the one above. You should always specify what columns you want returned. You can have some dramatic performance increases if you name which columns you want returned, especially if your rows are very wide.
You can also specify that the search is to query all Full‑Text columns in the table. Such a query would look like this:
Select * from table_name where CONTAINS(*, 'SearchPhrase')
If your search phrase contains an apostrophe, you will have to escape it with another apostrophe; so if you were searching on O'Brien, your search phrase would be O''Brien, e.g.:
Select * from table_name where CONTAINS(column_name, 'O''Brien')
Punctuation is ignored, but sometimes you will find unexpected results on single character searches or searches where there is a hit wrapped in brackets or parentheses. There is a bug in the Win2k version of the word breaker (infosoft.dll), which will hide certain character sequences from your search. All searches are order insensitive. So a search on SQL Server will return the same hits as Server SQL.
CONTAINS supports the following modifiers
You can also search on phrases as opposed to just a single word, but you have to wrap the phrase in double quotes.
Select * from table_name where CONTAINS(column_name, '"Peanut Butter and Jelly"')
You may run into a problem if your search phrase contains a noise word. You may have to parse out the noise words at the client (please refer to Chapter 9 for an example of this). For example, suppose your user does a search on "Drive In". This will result in an error message saying:
'Execution of a full‑text operation failed. A clause of the query contained only
You will have to parse the error message at the client. Here is an example of queries that will generate this message:
select * from authors where CONTAINS(au_lname,'"drive" and "in"')
Notice here that we have expanded each search argument of the search phrase and linked them with "and".
The below will return results successfully.
select * from authors where CONTAINS(au_lname,'"drive in"')
This leads right into a discussion of Boolean operators.
You can use Boolean operators on your search phrases (e.g. 'AND', 'OR', 'AND NOT' for each term of your search phrase). The Boolean operator combination OR NOT has no significance and is not supported. Why would you want to break down your search phrase into individual search terms? CONTAINS returns exact matches, so you might want to temper the exactness of the search and make it fuzzier???in other words, use a FREETEXT query. With a FREETEXT query, hits will be returned on documents that contain only some or all of your search terms. You can simulate this fuzziness using the CONTAINS predicate.
On the current Microsoft Support Web site (support.microsoft.com), there is a selection to do searches on all of your search terms (a logical AND), some of your search terms (a logical OR), or an exact match. A FREETEXT query will also do word stemming and nearness (a measure of how close search terms are to each other in a row). You may also find that you will get false hits when one word of your search phrase is in one column of a row and the other word of your search phrase is in another column of the same row.
The CONTAINS predicate allows you to do wildcarding. This is indicated by the * operator.
A search on the term run would return hits to run, runs, runt, running, runner???in short, anything that matches the first part of the search argument before the *. To get wildcarding to work, you must wrap it in double quotes. For instance, this search will return matches to micro* only:
Select * from table_name where CONTAINS(column_name, 'micro*')
This one will return matches to micro*, and Microsoft, microcosms, microscope, etc.
Select * from table_name where CONTAINS(column_name, '"micro*"')
To do word stemming where you are interested in having hits returned to rows that contain plural forms of the search term or any verb form of the search term, you have to use the formsof operator. This process is termed 'generation'.
Select * from table_name where (column_name,'formsof(Inflectional,SearchTerm)')
If you were using an generational search on the search term run you would get rows returned that contain the word run, runs, running, ran, but not to the word runts.
Full‑Text Search also allows you to define the nearness or closeness of words in your query. For instance, you might be interested in doing a search on "SQL Server Installation". Doing this as a phrase search???i.e. Select * from table_name where CONTAINS(column_name, '"SQL Server Installation"')???will not return hits where the row contains phrases such as "SQL Server has a pretty straightforward installation," which would probably contain information you are looking for. If you do a search such as Select * from table_name where CONTAINS(column_name, '"SQL" and "Server" and "Installation"'), you might get hits to a 2 Mg PDF on Exchange 2000 Server that contains the words "Exchange Server Installation Instructions" at the beginning, and on page 325 has a reference to "You can perform SQL‑like queries on your Web Storage System."
For the CONTAINS predicate, the nearness seems sensitive to a 10‑word boundary. If your search words are separated by more than 10 words, you will not receive a hit for that row. Within the CONTAINS predicate, you can use the nearness by using the NEAR keyword or the ~ symbol. The CONTAINS "nearness" is different from the CONTAINSTABLE or the FREETEXT Table "nearness", which is based on rank.
Nearness is not sensitive to order, so "SQL" near "Server" would return the same hits as "Server" near "SQL".
Select * from table_name where CONTAINS(column_name, 'SearchTerm1 near SearchTerm2')
Select * from table_name where CONTAINS(column_name, 'SearchTerm1 ~ SearchTerm2~SearchTerm3 ')
The order that the rows are ordered in your results set has no bearing on nearness. For instance, you may find that the first row returned in your results set has the two search terms farthest apart, and the next row has the two search terms closest together.
In the new version of SQL Server (code named Yukon, due to be released in 2005), Microsoft is currently planning on extending the nearness operator to allow you to specify the maximum word separation (10 words, 50 words, etc.)
Sometimes you need to do a search in which two topics are hopelessly intertwined, and yet you want to search on one topic alone. Consider doing a search on Monica Lewinsky and not wanting to wade through everything about the scandal with her and the president. Or try doing a search on Gary Condit without wading through everything on Chandra Levy.
Doing such an unqualified search results in too many hits containing references to the first topic and not enough on the second. Doing a search on the first topic and not the second topic (e.g., Select * from table_name where CONTAINS(column_name, '"SearchArgument1" and not "searchArgument2"')) results in no hits or hits that are not relevant.
Consider a search on Full‑Text Search. This will result in hits on Exchange Content Indexing, SQL Server Full‑Text Search, and Sharepoint Full‑Text Search. You would be tempted to merely NOT everything but SQL Server Full‑Text Search, but you would miss documents that discuss SQL Server Full‑Text Search and one or more of the other items. SQL Full‑Text Search allows you to weight search phrases so that one search phrase is weighted higher than the other. Another example would be a database of technical resumes. Doing a search on SQL database programming would be fruitless if you were searching for MSSQL Server database programmers, as you would get a lot of hits for Oracle PSQL database programmers. Using the "NOT" operator would remove all hits to programmers who program for both Oracle and MSSQL Server, so you would want to weight the search term Oracle lower than SQL Server.
Weight varies from 0 to 1, with 1 being the highest.
Here is an example:
Select * from table_name where CONTAINS(column_name, 'isabout("SQL Server
Full‑Text Search" WEIGHT(1),"Exchange Full‑Text Search"(WEIGHT(.1),'
Sharepoint Full‑TextSearch" WEIGHT(.1))
designed by :: smilla group