Using the SQL Server CONTAINS Function for Full-Text Search
DbVisualizer is the database client with the highest user satisfaction. It is used for development, analytics, maintenance, and more, by database professionals all over the world. It connects to all popular databases and runs on Win, macOS & Linux.
When working with large text data in SQL Server, simple LIKE queries may not be enough. The CONTAINS function allows full-text searches, making it easier to find words, phrases, and related terms.
How to use CONTAINS in SQL Server
To use CONTAINS, ensure your table has a full-text index.
SELECT *
FROM Product
WHERE CONTAINS((Name, Description), 'Laptop');
This retrieves any product where "Laptop" appears in either Name or Description.
Explore different ways to use CONTAINS
Find a single word.
WHERE CONTAINS(Description, 'powerful')
Search for an exact phrase.
WHERE CONTAINS(Description, '"with high-"')
Search using a word prefix.
WHERE CONTAINS(Description, '"W*"')
Proximity search.
WHERE CONTAINS(Description, 'NEAR((headphones, technology), 5)')
Inflectional search.
WHERE CONTAINS(Description, 'FORMSOF (INFLECTIONAL, game)')
FAQ
How does CONTAINS differ from LIKE?
LIKE is used for pattern matching, while CONTAINS allows advanced full-text searches.
LIKE doesn’t require an index; CONTAINS does.
Can CONTAINS search multiple columns?
Yes, specify them in parentheses.
WHERE CONTAINS((Column1, Column2), 'search_term')
How can I check if a column contains a substring?
Use CHARINDEX.
WHERE CHARINDEX('substring', ColumnName) > 0
Will CONTAINS work without a full-text index?
No, a full-text index is required; otherwise, the query will not work.
Conclusion
The SQL Server CONTAINS function is essential for ****full-text search queries. ****Unlike LIKE, it enables phrase searching, synonym matching, and word proximity queries.
Before using CONTAINS, ensure your database has full-text indexing enabled. If you need deeper insights and practical examples, read the article SQL CONTAINS Function: SQL Server Guide With Examples.
