The in operator selects documents that contain the specified search element in one or more document zones. Document zones are created for a text index in the following two scenarios:
When you create an index on two or more columns using sp_create_text_index, a document zone is created for each column in the text index (for more information, refer to “Specifying Multiple Columns When Creating a Text Index”). A document zone is not created when you create a text index on a single column. For example, if you specify the au_id and copy columns of the blurbs table when you create the text index, you can issue the following query:
select t1.score, t2.copy from i_blurbs t1, blurbs t2 where t1.id=t2.id and t1.score > 35 and t1.index_any = "gorilla <in> copy"
This returns rows that contain the word “gorilla” in the copy column. However, if you specify only the copy column of the blurbs table when you create the text index, this query does not return any rows.
When you create an index that uses a filter, a document zone is created for each tag in the document (for more information, see “Using Filters on Text That Contains Tags”). You can limit your search to a particular tag by specifying the tag name after the in operator. For example, to search for the word “automotive” in a “title” tag in an HTML document, specify:
select t1.score, t2.copy from i_blurbs t1, blurbs t2 where t1.id=t2.id and t1.score > 35 and t1.index_any = "automotive <in> title"
Text indexes utilizing filters can contain only one column.