-----------------------------------------------------------
New Message on BDOTNET
-----------------------------------------------------------
From: Arian_Intellect
Message 4 in Discussion
have a look at this
Transact-SQL Reference
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.. A word or phrase.
b.. The prefix of a word or phrase.
c.. A word near another word.
d.. A word inflectionally generated from another (for example, the word
drive is the inflectional stem of drives, drove, driving, and driven).
e.. 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 ServerT 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:
a.. NOT is applied before AND.
b.. 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" ' ).
c.. AND is applied before OR.
d.. 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.
Remarks
CONTAINS is not recognized as a keyword if the compatibility level is less
than 70. For more information, see sp_dbcmptlevel.
Examples
A. Use CONTAINS with <simple_term>
This example finds all products with a price of $15.00 that contain the word
"bottles."
USE Northwind
GO
SELECT ProductName
FROM Products
WHERE UnitPrice = 15.00
AND CONTAINS(QuantityPerUnit, 'bottles')
GO
B. Use CONTAINS and phrase in <simple_term>
This example returns all products that contain either the phrase "sasquatch
ale" or "steeleye stout."
USE Northwind
GO
SELECT ProductName
FROM Products
WHERE CONTAINS(ProductName, ' "sasquatch ale" OR "steeleye stout" ')
GO
C. Use CONTAINS with <prefix_term>
This example returns all product names with at least one word starting with
the prefix choc in the ProductName column.
USE Northwind
GO
SELECT ProductName
FROM Products
WHERE CONTAINS(ProductName, ' "choc*" ')
GO
D. Use CONTAINS and OR with <prefix_term>
This example returns all category descriptions containing the strings "sea"
or "bread."
USE Northwind
SELECT CategoryName
FROM Categories
WHERE CONTAINS(Description, '"sea*" OR "bread*"')
GO
E. Use CONTAINS with <proximity_term>
This example returns all product names that have the word "Boysenberry" near
the word "spread."
USE Northwind
GO
SELECT ProductName
FROM Products
WHERE CONTAINS(ProductName, 'spread NEAR Boysenberry')
GO
F. Use CONTAINS with <generation_term>
This example searches for all products with words of the form dry: dried,
drying, and so on.
USE Northwind
GO
SELECT ProductName
FROM Products
WHERE CONTAINS(ProductName, ' FORMSOF (INFLECTIONAL, dry) ')
GO
G. Use CONTAINS with <weighted_term>
This example searches for all product names containing the words spread,
sauces, or relishes, and different weightings are given to each word.
USE Northwind
GO
SELECT CategoryName, Description
FROM Categories
WHERE CONTAINS(Description, 'ISABOUT (spread weight (.8),
sauces weight (.4), relishes weight (.2) )' )
GO
H. Use CONTAINS with variables
This example uses a variable instead of a specific search term.
USE pubs
GO
DECLARE @SearchWord varchar(30)
SET @SearchWord ='Moon'
SELECT pr_info FROM pub_info WHERE CONTAINS(pr_info, @SearchWord)
See Also
FREETEXT
FREETEXTTABLE
Using the CONTAINS Predicate
WHERE
�1988-2000 Microsoft Corporation. All Rights Reserved.
Shaju Thomas
e-Cosmos Technologies Ltd.,
Mail: [EMAIL PROTECTED]
Phone(Off) : 51217038/39 Extn: 132
Mobile: +91 98455 21794
----- Original Message -----
From: "kothamahesh" <[EMAIL PROTECTED]>
To: "BDOTNET" <[EMAIL PROTECTED]>
Sent: Saturday, September 27, 2003 11:43 PM
Subject: Re: SQL Server - Full text indexing
-----------------------------------------------------------
New Message on BDOTNET
-----------------------------------------------------------
From: kothamahesh
Message 3 in Discussion
Thanks Thomas But how to do that , pls explain me.
-----------------------------------------------------------
To stop getting this e-mail, or change how often it arrives, go to your
E-mail Settings.
http://groups.msn.com/bdotnet/_emailsettings.msnw
Need help? If you've forgotten your password, please go to Passport Member
Services.
http://groups.msn.com/_passportredir.msnw?ppmprop=help
For other questions or feedback, go to our Contact Us page.
http://groups.msn.com/contact
If you do not want to receive future e-mail from this MSN group, or if you
received this message by mistake, please click the "Remove" link below. On
the pre-addressed e-mail message that opens, simply click "Send". Your
e-mail address will be deleted from this group's mailing list.
mailto:[EMAIL PROTECTED]
-----------------------------------------------------------
To stop getting this e-mail, or change how often it arrives, go to your E-mail
Settings.
http://groups.msn.com/bdotnet/_emailsettings.msnw
Need help? If you've forgotten your password, please go to Passport Member Services.
http://groups.msn.com/_passportredir.msnw?ppmprop=help
For other questions or feedback, go to our Contact Us page.
http://groups.msn.com/contact
If you do not want to receive future e-mail from this MSN group, or if you received
this message by mistake, please click the "Remove" link below. On the pre-addressed
e-mail message that opens, simply click "Send". Your e-mail address will be deleted
from this group's mailing list.
mailto:[EMAIL PROTECTED]
