DEV Community

Cover image for SQL Server CONTAINS Function: Quick Start Guide
DbVisualizer
DbVisualizer

Posted on

SQL Server CONTAINS Function: Quick Start Guide

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');
Enter fullscreen mode Exit fullscreen mode

Retrieves products with the term "powerful" in their descriptions.

Phrase Search

SELECT Id, Name, Description
FROM Product
WHERE CONTAINS(Description, '"with high-"');
Enter fullscreen mode Exit fullscreen mode

Finds products with the phrase "with high-" in the description.

Prefix Search

SELECT Id, Name, Description
FROM Product
WHERE CONTAINS(Description, '"W*"');
Enter fullscreen mode Exit fullscreen mode

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)');
Enter fullscreen mode Exit fullscreen mode

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.

Image of Timescale

Timescale – the developer's data platform for modern apps, built on PostgreSQL

Timescale Cloud is PostgreSQL optimized for speed, scale, and performance. Over 3 million IoT, AI, crypto, and dev tool apps are powered by Timescale. Try it free today! No credit card required.

Try free

Top comments (0)

AWS Security LIVE!

Tune in for AWS Security LIVE!

Join AWS Security LIVE! for expert insights and actionable tips to protect your organization and keep security teams prepared.

Learn More

👋 Kindness is contagious

Explore a sea of insights with this enlightening post, highly esteemed within the nurturing DEV Community. Coders of all stripes are invited to participate and contribute to our shared knowledge.

Expressing gratitude with a simple "thank you" can make a big impact. Leave your thanks in the comments!

On DEV, exchanging ideas smooths our way and strengthens our community bonds. Found this useful? A quick note of thanks to the author can mean a lot.

Okay