Full-text ins Function

Contains

It is a statement used to search for precise or fuzzy (less precise) matching single words or slices of data rows containing character data types, matches the text or permission of the text within a specified distance. You can search for INS ︰

Single Word or slice.

The beginning of a single word or slice.

A single word that is close to another word.

Single words changed from another word (for example, single-word drive can be transformed into drives, drove, driving and driven ).

A single word that is more powerful than a specific addition.

You can use the predicate statement to search for specific segments in the resource list. Of course, such queries can also be like statements. However, many contains provide more text query functions than like. In addition, unlike the like statement, the contains search condition must be different in size.

AppendixFor the statement (most of the Latin series) that requires division by size, full-text search and query operations will be conducted in a non-division by size. However, in Japanese, there are multiple pronunciation and spelling methods, and the concept of Pinyin normalization is similar to non-regional differences (for example, Kana = insensiti.pdf ). This spelling method is not yet supported.

False settings must beNorthwindSearch for "bean curd" in information. If you use the contains statement, this will be a very simple query.

USE Northwind GOSELECT DescriptionFROM CategoriesWHERE Description LIKE '%bean curd%'GO

 

Alternatively, use contains:

USE NorthwindGOSELECT DescriptionFROM CategoriesWHERE CONTAINS(Description, ' "bean curd" ')GO

The words in the contains statement use the function data representation. The first parameter is used as the name of the Data row to be searched. The second parameter is used as a condition for full-text search. The Search Condition (this parameter is "bean curd") is sometimes regarded as a temporary component and is composed of multiple words, which will be described later.

The contains Statement supports the keyword encoding method, which can be searched in the character row:

• One or more specific word groups and/or segments ).
  

  A word group is one or more characters without spaces or standards. A valid slice may contain multiple word groups and spaces, which may or may not have any standards. For example, croissant is a word group, while Caf au lait is a piece of cake. This type of word group and film are called simple words.
  

• The font of a specific word group (derived tokens ).
  

  For example, if the font of search drive is changed, if the data columns of the data table contain drive, drives, drove, driving, and driven, the result set will all appear, because this is a variant of the font produced by drive.
  

• A word group or segment that starts with a specified text ).
  

  Each word group in a segmentation is a prefix. For example, auto Tran * is compatible with the automatic transmission (automatic transmission) and automobile transducer ).
  

• Use the Authority value of a word group or slice ).
  

  For example, to search for a word group with a higher permission than other word groups. It returns the result of checking the shard.

• A word group or slice that is near another word group or slice ).
  

  For example, you can refer to the information column of ice near the hockey word, or the information column of ice skating film near the ice hockey film category.

The words "and" and "or" can be used to combine a number of such categories. For example, find the item columns containing latte and New York-style bagel in the same region using full-text indexing. In addition, using and not can cancel certain shards, such as including bagel But not cream cheese.

When using contains, please remember that SQL server will search for the stem character group in the condition. The stem character group refers to a, And, is, or the. This type of word often appears, but it does not provide practical help for searching specific texts.

Limit Method

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]
)

Reference

Column

In order to provide the full-text search information row name. The data row of the character string type is a valid full-text search.

*

All the data rows that are listed as full-text indexes in the specified data table are used to search for including the search criteria. If the from clause contains multiple data tables, * the data table name must be used for verification.

<Contains_search_condition>

SpecifyColumnText to be searched. The variable cannot be used in the search condition.

Word

It is a character string with no space or marker.

Phrase

It is one or more words with spaces between each word.

AppendixA part of the statement (for example, an Asian speaker) can contain one or more words without spaces.

 

<Simple_term>

Specifies a positive character (one or more characters with no space or standard character in the single-digit metadata Statement) or slice (one or more leading words separated by spaces or dots in a single-digit metadatabase ). Valid ticket types include "blue berry", blueberry, and "Microsoft SQL Server ". The Slice should be enclosed by a comma. The words in the slice must be displayed in the order specified in <contains_search_condition>. The characters in a single character or slice search are not in size. The words (such as A, and or the) in the full-text search row cannot exist in the full-text search. If you use words in a single-word search, SQL Server automatically points out that the words can only appear in the search results. SQL Server contains standard words in/MSSQL/ftdata/sqlserver/config.

The identifier is ignored. Therefore, contains (testing, "computer failure") will find the consistent value in the data column: "Where is my computer? Failure to find it wocould be expensive .".

<Prefix_term>

Specifies a single word or slice starting with the specified text. Enclose the prefix with a forward ("") and add the asterisk (*) before the end of the line, all texts starting with the single word specified before the star match. This clause should specify the following: a star can contain zero, one, or more characters (single-character or single-character. If the delimiter Delimiter is not used to separate text and star numbers, for example, contains (column, 'text * '), the full text delimiter will convert star numbers into characters, and search for information that fully conforms to text.

When there is <prefix_term> In the slice, each word contained in the slice is treated as a different character. Therefore, the query results of the specified "local wine *" header can conform to any data columns with the characters "local winery", "locally wined and dined", and so on.

<Generation_term>

The change of the original word to be searched must conform to this word.

Inflectional

The specified metric data and unit data, as well as the name, dynamic word, and descriptive word must be consistent with the neutral form. The time periods of the words should also be consistent.

When <generation_term> is specified, <simple_term> does not conform to the name and action words.

<Proximity_term>

The word or slice to be matched must be close to another word. The <proximity_term> operation is similar to the and operator. Both require that there be multiple words or slices in the retrieved row. The closer <proximity_term> is, the better it can be.

Near | ~

Indicates near or ~ The word or slice on the left side of the operator should be approximately near or ~ A single word or slice on the right side of the operator. Multiple similar regions can be connected, for example:

a NEAR b NEAR c 

This indicates that single-word or slice a should be similar to single-word or slice B, while B should be similar to single-word or slice C.

Microsoft SQL Server sorts the vertical order between the left and the right text or slice. The low order value (for example, 0) indicates that the two words are too different. If the difference between the specified word or slice is too large, you can check whether the sequence is correct, but the Sequence Value of the result is very low (0 ). However, if <contains_search_condition> contains only one or more near approximate response values, SQL server does not return data columns with a low order value of 0. If you need hierarchical metadata, Please configure containstable.

<Weighted_term>

The specified data columns (which are retrieved from the check box) Comply with single words or slice clear orders, and each permission value is selected.

Isabout

Specify the <weighted_term> keyword.

Weight (Weight_value )

Specify the value of privilege between 0.0 and 1.0. Each component in <weighted_term> may contain Weight_value. Weight_valueIt is a method for changing how each part of the query result maps the sequence value assigned to each item column that meets the query condition. The authority determines whether all the components of <weighted_term> can be used together to determine whether they meet the requirements. If any isabout metric is met, the returned item column is used to determine whether the permission value is assigned. If you want to determine the sequence value of each returned item column that indicates the degree of conformity of the returned item column, please specify instable.

And | and not | or

Specify two operation records that contain the search condition. When <contains_search_condition> contains a group, the system first evaluates these groups. The following rules can be applied when the operator used with the operator to search for a restricted group is used:

• Not used before and.

• Not only after and, for example, and not. The or not operator cannot be used. Not (for example, contains (Mycolumn, 'Not "phrase_to_search_for "').
• And is used before or.
• The Blin operators of (and, or) of the same type are related, so they can be applied in any order.

N

Indicates that you can specify multiple replacement characters that contain the Search Condition and signature.

Zookeeper

If the compatibility limit is lower than 70, the contains cannot be used as a parameter. To renew your database, please renew sp_dbcmptlevel.

Example A. Use contains with <simple_term>

In this example, we will find all products with a single word "bottles" for $15.00.

USE NorthwindGOSELECT ProductNameFROM ProductsWHERE UnitPrice = 15.00   AND CONTAINS(QuantityPerUnit, 'bottles')GO

B. Use the slice volumes in contains and <simple_term>

In this example, all products containing "Sasquatch Ale" or "steeleye stout" are returned.

USE NorthwindGOSELECT ProductNameFROM ProductsWHERE CONTAINS(ProductName, ' "sasquatch ale" OR "steeleye stout" ')GO

C. Use contains with <prefix_term>

This example will returnProductnameThere must be at least one word in the item line with the name of all products starting with Chos.

USE NorthwindGOSELECT ProductNameFROM ProductsWHERE CONTAINS(ProductName, ' "choc*" ')GO

D. Use contains and or with <prefix_term>

In this example, all the class descriptions containing the string "sea" or "Bread" are returned.

USE NorthwindSELECT CategoryNameFROM CategoriesWHERE CONTAINS(Description, '"sea*" OR "bread*"')GO

E. Use contains with <proximity_term>

In this example, the name of all products with the word "boysenberry" similar to "spread" is returned.

USE NorthwindGOSELECT ProductNameFROM ProductsWHERE CONTAINS(ProductName, 'spread NEAR Boysenberry')GO

F. Use contains with <generation_term>

In this example, we will search for all the products with single-word dry: Dried, drying, and so on.

USE NorthwindGOSELECT ProductNameFROM ProductsWHERE CONTAINS(ProductName, ' FORMSOF (INFLECTIONAL, dry) ')GO

G. Use contains with <weighted_term>

This example searches for the names of all products with a single word "spread", "sauces", or "relishes". Each word has different permissions.

USE NorthwindGOSELECT CategoryName, DescriptionFROM CategoriesWHERE CONTAINS(Description, 'ISABOUT (spread weight (.8),    sauces weight (.4), relishes weight (.2) )' )GO

H. Use ins with variable numbers

This example uses change to replace a specific search keyword.

USE pubsGODECLARE @SearchWord varchar(30)SET @SearchWord ='Moon'SELECT pr_info FROM pub_info WHERE CONTAINS(pr_info, @SearchWord)