In SQL Server, the CONTAINS and FREETEXT predicates are used for performing full-text searches on textual data within a database. They allow you to search for words, phrases, or patterns in text columns that are indexed with a full-text index.
1. CONTAINS Predicate
The CONTAINS predicate is used for more precise searches where you can specify exactly what you are looking for, including the use of logical operators, wildcards, and proximity searching.
Syntax:
CONTAINS(column_name, 'search_condition')
Examples:
- Searching for an exact phrase:
SELECT * FROM table_name
WHERE CONTAINS(column_name, '"exact phrase"');
- Searching for a single word:
SELECT * FROM table_name
WHERE CONTAINS(column_name, 'word');
- Using boolean operators:
SELECT * FROM table_name
WHERE CONTAINS(column_name, '("word1" AND "word2") OR "word3"');
- Using wildcard characters:
SELECT * FROM table_name
WHERE CONTAINS(column_name, 'word*'); -- Searches for "word" followed by any characters
2. FREETEXT Predicate
The FREETEXT predicate is used for searching natural language terms. It allows for a broader match and does not require the use of specific syntax. It looks for meaning rather than an exact match of phrases.
Syntax:
FREETEXT(column_name, 'search_terms')
Examples:
- Searching for multiple keywords in a natural language context:
SELECT * FROM table_name
WHERE FREETEXT(column_name, 'search terms');
Key Differences between CONTAINS and FREETEXT
- Precision vs. Broadness: CONTAINS is more precise and allows for complex queries with logical operators, whereas FREETEXT is broader and interprets the meaning of the input.
- Syntax Requirements: CONTAINS has strict syntax requirements, while FREETEXT can interpret plain language search terms.
Important Considerations
1. Full-Text Index: To use these predicates, the relevant column must be indexed with a full-text index. This can be set up using SQL Server Management Studio or through SQL commands.
2. Performance: Full-text searches can be performance-intensive, especially on large datasets, so it's essential to optimize your queries and indexes accordingly.
3. Language: The language of the full-text index affects how words are processed, especially concerning stemming and stopwords (common words that are ignored).
By understanding and utilizing these predicates effectively, you can implement powerful search functionality in your database applications.