Solved

Difference between FREETEXT and CONTAINS functions?

Posted on 2002-05-28
1
4,771 Views
Last Modified: 2012-06-27
Hellow!

What is the difference between FREETEXT and CONTAINS functions?

If i search word or phrase in text or ntext field,
what function use better?

thank you!
0
Comment
Question by:kaspiton
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
1 Comment
 
LVL 35

Accepted Solution

by:
David Todd earned 80 total points
ID: 7040590
Hi

From BOL

FREETEXT
Is a predicate used to search columns containing character-based data types for values that match the meaning and not the exact wording of the words in the search condition. When FREETEXT is used, the full-text query engine internally "word-breaks" the freetext_string into a number of search terms and assigns each term a weight and then finds the matches.

Syntax
FREETEXT ( { column | * } , 'freetext_string' )

Arguments
column

Is the name of a specific column that has been registered for full-text searching. Columns of the character string data types are valid columns for full-text searching.

*

Specifies that all columns that have been registered for full-text searching should be used to search for the given freetext_string.

freetext_string

Is text to search for in the specified column. Any text, including words, phrases or sentences, can be entered. There is no concern about syntax.

Remarks
Full-text queries using FREETEXT are less precise than those full-text queries using CONTAINS. The Microsoft. SQL Server full-text search engine identifies important words and phrases. No special meaning is given to any of the reserved keywords or wildcard characters that typically have meaning when specified in the <contains_search_condition> parameter of the CONTAINS predicate.

FREETEXT is not recognized as a keyword if the compatibility level is less than 70. For more information, see sp_dbcmptlevel.

CONTAINS
Is a predicate used to search columns containing character-based data types for precise or fuzzy (less precise) matches to single words and phrases, the proximity of words within a certain distance of one another, or weighted matches. CONTAINS can search for:

A word or phrase.


The prefix of a word or phrase.


A word near another word.


A word inflectionally generated from another (for example, the word drive is the inflectional stem of drives, drove, driving, and driven).


A word that has a higher designated weighting than another word.
Syntax
CONTAINS
    ( { column | * } , '< contains_search_condition >'
    )

< contains_search_condition > ::=
        { < simple_term > 
        | < prefix_term > 
        | < generation_term > 
        | < proximity_term > 
        | < weighted_term > 
        }
        | { ( < contains_search_condition > )
        { AND | AND NOT | OR } < contains_search_condition > [ ...n ]
        }

< simple_term > ::=
    word | " phrase "

< prefix term > ::=
    { "word * " | "phrase * " }

< generation_term > ::=
    FORMSOF ( INFLECTIONAL , < simple_term > [ ,...n ] )

< proximity_term > ::=
    { < simple_term > | < prefix_term > }
    { { NEAR | ~ } { < simple_term > | < prefix_term > } } [ ...n ]

< weighted_term > ::=
    ISABOUT
        ( { {
                < simple_term > 
                | < prefix_term > 
                | < generation_term > 
                | < proximity_term > 
                }
            [ WEIGHT ( weight_value ) ]
            } [ ,...n ]
        )

Arguments
column

Is the name of a specific column that has been registered for full-text searching. Columns of the character string data types are valid full-text searching columns.

*

Specifies that all columns in the table registered for full-text searching should be used to search for the given contains search condition(s). If more than one table is in the FROM clause, * must be qualified by the table name.

<contains_search_condition>

Specifies some text to search for in column. Variables cannot be used for the search condition.

word

Is a string of characters without spaces or punctuation.

phrase

Is one or more words with spaces between each word.



Note  Some languages, such as those in Asia, can have phrases that consist of one or more words without spaces between them.


<simple_term>

Specifies a match for an exact word (one or more characters without spaces or punctuation in single-byte languages) or a phrase (one or more consecutive words separated by spaces and optional punctuation in single-byte languages). Examples of valid simple terms are "blue berry", blueberry, and "Microsoft SQL Server". Phrases should be enclosed in double quotation marks (""). Words in a phrase must appear in the same order as specified in <contains_search_condition> as they appear in the database column. The search for characters in the word or phrase is case insensitive. Noise words (such as a, and, or the) in full-text indexed columns are not stored in the full-text index. If a noise word is used in a single word search, SQL Server returns an error message indicating that only noise words are present in the query. SQL Server includes a standard list of noise words in the directory \Mssql\Ftdata\Sqlserver\Config.

Punctuation is ignored. Therefore, CONTAINS(testing, "computer failure") matches a row with the value, "Where is my computer? Failure to find it would be expensive."

<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, as in CONTAINS (column, 'text*'), full-text search considers the asterisk as a character and will search for exact matches to text*.

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.

<generation_term>

Specifies a match of words when the included simple terms include variants of the original word for which to search.

INFLECTIONAL
Specifies that the plural and singular, as well as the gender and neutral forms of nouns, verbs, and adjectives should be matched. The various tenses of verbs should be matched too.
A given <simple_term> within a <generation_term> will not match both nouns and verbs.

<proximity_term>

Specifies a match of words or phrases that must be close to one another. <proximity_term> operates similarly to the AND operator: both require that more than one word or phrase exist in the column being searched. As the words in <proximity_term> appear closer together, the better the match.

NEAR | ~
Indicates that the word or phrase on the left side of the NEAR or ~ operator should be approximately close to the word or phrase on the right side of the NEAR or ~ operator. Multiple proximity terms can be chained, for example:
a NEAR b NEAR c
This means that word or phrase a should be near word or phrase b, which should be near word or phrase c.

Microsoft. SQL Server ranks the distance between the left and right word or phrase. A low rank value (for example, 0) indicates a large distance between the two. If the specified words or phrases are far apart from each other, the query is considered to be satisfied; however, the query has a very low (0) rank value. However, if <contains_search_condition> consists of only one or more NEAR proximity terms, SQL Server does not return rows with a rank value of 0. For more information about ranking, see CONTAINSTABLE.

<weighted_term>

Specifies that the matching rows (returned by the query) match a list of words and phrases, each optionally given a weighting value.

ISABOUT
Specifies the <weighted_term> keyword.

WEIGHT (weight_value)
Specifies a weight value which is a number from 0.0 through 1.0. Each component in <weighted_term> may include a weight_value. weight_value is a way to change how various portions of a query affect the rank value assigned to each row matching the query. Weighting forces a different measurement of the ranking of a value because all the components of <weighted_term> are used together to determine the match. A row is returned if there is a match on any one of the ISABOUT parameters, whether or not a weight value is assigned. To determine the rank values for each returned row that indicates the degree of matching between the returned rows, see CONTAINSTABLE.
AND | AND NOT | OR

Specifies a logical operation between two contains search conditions. When <contains_search_condition> contains parenthesized groups, these parenthesized groups are evaluated first. After evaluating parenthesized groups, these rules apply when using these logical operators with contains search conditions:

NOT is applied before AND.


NOT can only occur after AND, as in AND NOT. The OR NOT operator is not allowed. NOT cannot be specified before the first term (for example, CONTAINS (mycolumn, 'NOT "phrase_to_search_for" ' ).


AND is applied before OR.


Boolean operators of the same type (AND, OR) are associative and can therefore be applied in any order.
n

Is a placeholder indicating that multiple contains search conditions and terms within them can be specified.

Regards
  David
0

Featured Post

NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

615 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question