How do you use the CONTAINS and FREETEXT predicates for full-text search?
FrankMl
Posted by FrankMl
Last Updated: June 12, 2024
search predicates
In SQL Server, the CONTAINS and FREETEXT predicates are used for full-text searches to find words or phrases in textual columns of a database. Here's a breakdown of each and how to use them:
CONTAINS Predicate
The CONTAINS predicate is used to search for specific words or phrases within a full-text indexed column. You can also use it to search for proximity, variations of words, and other conditions.
Syntax:
CONTAINS(column_name, search_condition)
Examples:
1. Simple search: 
SELECT *
   FROM YourTable
   WHERE CONTAINS(YourColumn, 'search_term');
2. Phrase search: 
SELECT *
   FROM YourTable
   WHERE CONTAINS(YourColumn, '"exact phrase"');
3. Boolean operators: 
SELECT *
   FROM YourTable
   WHERE CONTAINS(YourColumn, 'term1 AND term2');
4. Wildcards: 
SELECT *
   FROM YourTable
   WHERE CONTAINS(YourColumn, 'term*');  -- Matches any words starting with 'term'.
5. Near operator for proximity: 
SELECT *
   FROM YourTable
   WHERE CONTAINS(YourColumn, 'NEAR((term1,term2), distance)');
FREETEXT Predicate
The FREETEXT predicate is used for searching the meaning of the words in a column rather than exact matches. It looks for words that are synonyms or variations of the search terms.
Syntax:
FREETEXT(column_name, search_string)
Example:
1. Basic search: 
SELECT *
   FROM YourTable
   WHERE FREETEXT(YourColumn, 'search terms');
2. Assumes broader search: - If you search for "car," it may also return results containing "automobile" or "vehicle."
Key Differences
- CONTAINS allows for more precise searches (exact phrases, Boolean logic, wildcards). - FREETEXT delivers more flexible and context-aware searches based on semantics rather than strict term matching.
Requirements
Before using these predicates, ensure that: - Full-Text Search is enabled on your SQL Server instance. - The columns you intend to search are part of a full-text index.
Full-Text Index Creation
To create a full-text index, you can use the following SQL command: 
CREATE FULLTEXT INDEX ON YourTable(YourColumn) 
   KEY INDEX YourPrimaryKeyIndexName;
This setup enables effective full-text searching capabilities across your textual data within SQL Server.
Conclusion
Use CONTAINS when you need precision and control over your searches, and FREETEXT when you want to capture a broader interpretation of the search terms.
Default Image