The CONTAINS function in SQL Server is designed for advanced full-text search operations. It allows you to search for specific words or phrases across text columns. Here’s a quick guide to get you started with practical examples.
Single Term Search
SELECT Id, Name, Description
FROM Product
WHERE ListPrice > 20 AND CONTAINS(Description, 'powerful');
Retrieves products with the term "powerful" in their descriptions.
Phrase Search
SELECT Id, Name, Description
FROM Product
WHERE CONTAINS(Description, '"with high-"');
Finds products with the phrase "with high-" in the description.
Prefix Search
SELECT Id, Name, Description
FROM Product
WHERE CONTAINS(Description, '"W*"');
Returns products where a word in the description starts with "W".
Word Proximity Search
SELECT Id, Name, Description
FROM Product
WHERE CONTAINS(Description, 'NEAR((headphones, technology), 5)');
Searches for "headphones" close to "technology" within five words.
FAQ Section
LIKE or CONTAINS in SQL Server?
LIKE is for simple searches, while CONTAINS is for advanced, indexed full-text searches.
Can CONTAINS be used on multiple columns?
Yes, list the columns separated by commas in the search condition.
Does CONTAINS require a full-text index?
Yes, without a full-text index, CONTAINS won’t work.
How to check if a string contains a substring in SQL?
Use CHARINDEX to search for a substring within a column.
Conclusion
The SQL CONTAINS function is a versatile tool for performing full-text searches in SQL Server. For more examples and a deeper dive, check out the article SQL CONTAINS Function: SQL Server Guide With Examples.
Top comments (0)