Also, you cannot use the LIKE predicate to query formatted binary data. In contrast to full-text search, the LIKE Transact-SQL predicate works on character patterns only. Compare Full-Text Search queries to the LIKE predicate WHERE CONTAINS(candidate_resume, ''SQL Server'') AND candidate_division = 'DBA' įor more information, see Query with Full-Text Search. Recruitment scenario-searching for job candidates that have experience working with SQL Server: SELECT candidate_name,SSN WHERE CONTAINS(product_description, ''Snap Happy 100EZ'' OR FORMSOF(THESAURUS,''Snap Happy'') OR '100EZ') For example:Į-business-searching for a product on a website: SELECT product_id However, the search goals of a given business scenario influence the structure of the full-text queries. For example, searching for 'Aluminum' or 'aluminum' returns the same results.įull-text queries use a small set of Transact-SQL predicates ( CONTAINS and FREETEXT) and functions ( CONTAINSTABLE and FREETEXTTABLE). Words or phrases using weighted values ( weighted term)įull-text queries are not case-sensitive. Synonymous forms of a specific word ( thesaurus) Inflectional forms of a specific word ( generation term)Ī word or phrase close to another word or phrase ( proximity term) One or more specific words or phrases ( simple term)Ī word or a phrase where the words begin with specified text ( prefix term) These queries can search for any of the following: Full-Text Search queriesĪfter columns have been added to a full-text index, users and applications can run full-text queries on the text in the columns. A match occurs when a target document contains all the terms specified in the full-text query, and meets any other search conditions, such as the distance between the matching terms. A full-text query returns any documents that contain at least one match (also known as a hit). Full-text queries can include simple words and phrases or multiple forms of a word or phrase. Each full-text index indexes one or more columns from the table, and each column can use a specific language.įull-text queries perform linguistic searches against text data in full-text indexes by operating on words and phrases based on the rules of a particular language such as English or Japanese.

These columns can have any of the following data types: char, varchar, nchar, nvarchar, text, ntext, image, xml, or varbinary(max) and FILESTREAM. OverviewĪ full-text index includes one or more character-based columns in a table.

If you didn't select Full-Text Search when you installed SQL Server, run SQL Server Setup again to add it. Full-Text Search is an optional component of the SQL Server Database Engine.

Full text search