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:
CONTAINS
( {column | *}, '<contains_search_condition>'
)
<contains_search_condition> ::=
{
| <generation_term>
| <prefix_term>
| <proximity_term>
| <simple_term>
| <weighted_term>
}
| { (<contains_search_condition>)
{AND | AND NOT | OR} <contains_search_condition> [...n]
}
<weighted_term> ::=
ISABOUT
( { {
<generation_term>
| <prefix_term>
| <proximity_term>
| <simple_term>
}
[WEIGHT (weight_value)]
} [,...n]
)
<generation_term> ::=
FORMSOF (INFLECTIONAL, <simple_term> [,...n] )
<prefix term> ::=
{ "word * " | "phrase * " }
<proximity_term> ::=
{<simple_term> | <prefix_term>}
{ {NEAR | ~} {<simple_term> | <prefix_term>} } [...n]
<simple_term> ::=
word | " phrase "
Note Some languages, such as those in the Far East, can have phrases that consist of one or more words without spaces between them.
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 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.
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.”.
CONTAINS is not recognized as a keyword if the compatibility level is less than 70. For more information, see sp_dbcmptlevel.
This example finds all products with a price of $15.00 that contains the word bottles.
USE Northwind
GO
SELECT ProductName
FROM Products
WHERE UnitPrice = 15.00
AND CONTAINS(QuantityPerUnit, 'bottles')
GO
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
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
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
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
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
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
file: /Techref/language/sql/contains.htm, 14KB, , updated: 2001/12/17 16:51, local time: 2024/9/9 08:30,
owner: JMN-EFP-786,
18.118.137.167:LOG IN
|
©2024 These pages are served without commercial sponsorship. (No popup ads, etc...).Bandwidth abuse increases hosting cost forcing sponsorship or shutdown. This server aggressively defends against automated copying for any reason including offline viewing, duplication, etc... Please respect this requirement and DO NOT RIP THIS SITE. Questions? <A HREF="http://linistepper.com/techref/language/sql/contains.htm"> CONTAINS</A> |
Did you find what you needed? |