SQL

SQL CONTAINS Function: SQL Server Guide With Examples

intro

Explore the power of the CONTAINS SQL Server function and understand how it works with real-world examples.

Tools used in the tutorial
Tool Description Link
Dbvisualizer DBVISUALIZER
TOP RATED DATABASE MANAGEMENT TOOL AND SQL CLIENT

Explore the power of the CONTAINS SQL Server function and understand how it works with real-world examples.

Full-text string searching is essential for finding data in the database based on advanced query criteria. This process is complex, which is why SQL Server offers specialized functions to simplify it. Among those is the SQL CONTAINS function, a powerful tool designed for full-text search queries.

In this article, you will dig into the world of CONTAINS. Uncover its syntax, mechanics, and practical applications through several examples.

Embark on a journey to understand the capabilities of CONTAINS in SQL Server!

What Is the CONTAINS Function in SQL Server?

CONTAINS is a SQL Server function to search for a word or phrase in one or more text columns using precise or fuzzy matching. Specifically, SQL CONTAINS is a predicate to use in the WHERE clause to perform full-text search. The column or columns to search in must be of character-based data types. Also, they must be full-text indexed columns.

In detail, the SQL Server CONTAINS function can search for:

  • A specific word.
  • A particular phrase.
  • The prefix of a word.
  • The prefix of a phrase.
  • A word within a certain distance of another.
  • A word inflectionally generated from another (e.g., the word “query” is the inflectional stem of “queries,” “queried,” and ”querying”).
  • A word that is a synonym of another word based on the specified thesaurus (e.g., the word "discover" can have synonyms such as "find," "uncover," and "reveal").

How to Use the SQL CONTAINS Function in Transact-SQL

The CONTAINS SQL Server function should be used in a Transact-SQL SELECT query as below:

Copy
        
1 SELECT * 2 FROM <TableName> 3 WHERE CONTAINS (<ColumnName> | ( <ColumnList> ) | *, '<SearchCondition>')

The arguments supported by the function are:

  • ColumnName: The name of the full-text indexed column in the <TableName> table to apply <SearchCondition> to.
  • ColumnList: The list of columns, separated by commas, and enclosed in parentheses to apply <SearchCondition> to.
  • *: Specifies that the SQL CONTAINS string search query will involve all full-text indexed columns in the <TableName> table.
  • <SearchCondition>: A nvarchar string that contains the text to search for and the conditions for a match. This can also include SQL Server full-text search operators such as OR, AND, and AND NOT. Check out the docs to find out all the full-text search capabilities offered by Transact-SQL.

Consider the CONTAINS SQL example query below:

Copy
        
1 SELECT Id, Name, Description 2 FROM Product 3 WHERE CONTAINS((Name, Description), 'Laptop');

That will return the products whose Name and Description columns contain the word “Laptop.” Based on your table’s collation, CONTAINS will be case-sensitive or case-insensitive. As you can see from the results below, it is case-insensitive in this case:

Executing the SQL CONTAINS query in DbVisualizer
Executing the SQL CONTAINS query in DbVisualizer

If Name and Description columns were not full-text indexed, the query would fail with:

Cannot use a CONTAINS or FREETEXT predicate on table or indexed view '<YourTable>' because it is not full-text indexed.

To avoid that, you need to define a FULLTEXT index involving the columns to apply CONTAINS to. Take a look at the SQL Server documentation to learn how.

Unfortunately, the full-text search component is optional in SQL Server. Thus, your FULLTEXT index creation query may fail with:

[Code: 7609, SQL State: S1000] Full-Text Search is not installed, or a full-text component cannot be loaded.

Learn how to install it in the next section. If this is not your case, go to the next main chapter.

Add the Full-Text Search Component on SQL Server 2022

Launch the query below to make sure that the Full-Text Search component is installed:

Copy
        
1 SELECT SERVERPROPERTY('IsFullTextInstalled');

If it returns 1, it is already installed. However, if it returns 0, you need to add it to your SQL Server.

Download the installer of the Developer edition of SQL Server. Double-click it, and select the “Custom” option. What for the installer to load and click on the “Installation” menu entry on the right. Next, select “New SQL Server standalone installation or add features to an existing installation” as in the image below:

Click on the first option.
Click on the first option.

Keep clicking the “Next” button until you reach the following screen:

All operations have been completed and you can click “Next”.
All operations have been completed and you can click “Next”.

Click “Next” again and select the second option as follows:

Click “Next”.
Click “Next”.

Uncheck the “Azure Extension for SQL Server” option and press the “Next” button:

Make sure to uncheck the first option.
Make sure to uncheck the first option.

Check the “Full-Text and Semantic Extractions for Search” component, click “Next,” and wait for the installation process to end.

Find the “Full-Text” SQL Server component and install it.
Find the “Full-Text” SQL Server component and install it.

Note that you can follow this procedure to install other essential additional modules such as “SQL Server Replication.”

Now, reconnect to your SQL Server and you are ready to use the SQL CONTAINS function!

SQL CONTAINS String: Use Cases

Let's learn how to use CONTAINS in SQL Server by exploring the most common possible scenarios through real-world examples.

The SQL string CONTAINS queries below will be executed in DbVisualizer, the database client with the highest user satisfaction in the market! The table the queries will be run on is the following one:

The data in the “Product” table as presented by DbVisualizer.
The data in the “Product” table as presented by DbVisualizer.

As you can see, it stores information about some products.

Search for a Single Term

The following example finds all products with a price of more than 20 units of value that contain the word “powerful” in their description:

Copy
        
1 SELECT Id, Name, Description 2 FROM Product 3 WHERE ListPrice > 20 AND CONTAINS(Description, 'powerful');
Two products contain the word “powerful” in their description.
Two products contain the word “powerful” in their description.

Search for a Phrase

The SQL CONTAINS example below returns all products that contain the “with high-” phrase:

Copy
        
1 SELECT Id, Name, Description 2 FROM Product 3 WHERE CONTAINS(Description, '"with high-"');
Many products contain the desired phrase.
Many products contain the desired phrase.

Note the use of the double quotes. Without them the query will fail with:

[Code: 7630, SQL State: S1000]  Syntax error near 'high-' in the full-text search condition 'with high-'.

This is because double quotes are required on Transact-SQL full-text search conditions involving multiple words or special operators.

Keep in mind that different database management systems may interpret special characters differently. For example, in MySQL:

  • “+” means “must contain.”
  • “-” means “must not contain.”
  • “*” denotes a wildcard.
  • Any text wrapped in double quotes ("") means “give me an exact match.”

Search for the Prefix of a Word

This query retrieves all products whose name contains at least one word starting with the “W” character:

Copy
        
1 SELECT Id, Name, Description 2 FROM Product 3 WHERE CONTAINS(Description, '"W*"');
Note the wildard “*” operator.
Note the wildard “*” operator.

Notice the use of the double quotes and the “*” character to specify the prefix syntax in the CONTAINS SQL function.

Word Proximity Search

The following query searches for all products whose description contains the word ”headphones” within 5 search terms of the word "technology" in the specified order (“headphones” must precede “technology”):

Copy
        
1 SELECT Id, Name, Description 2 FROM Product 3 WHERE CONTAINS(Description, 'NEAR((headphones, technology), 5)');
The description of the matching product contains the two words within the specified distance.
The description of the matching product contains the two words within the specified distance.

Find out more about the NEAR full-text operator.

Search for Inflectional Forms

This query searches for all products with words of the form ride: riding, ridden, and so on.

Copy
        
1 SELECT Id, Name, Description 2 FROM Product 3 WHERE CONTAINS(Description, 'FORMSOF (INFLECTIONAL, game)');
The resulting product contains the word “gaming” in its description.
The resulting product contains the word “gaming” in its description.

Note that the product “Gaming Console” contains the word “gaming” but not “game.” That is still a match as “gaming” is an inflectional form of “game.”

Search Through Logical Operators

The CONTAINS predicate in the query below will search for descriptions that contain both the word “high” and the word “powerful:”

Copy
        
1 SELECT Id, Name, Description 2 FROM Product 3 WHERE CONTAINS(Description, 'high AND powerful');
Note that the resulting product contains the two words as desired.
Note that the resulting product contains the two words as desired.

Notice the use of the AND operator. Similarly, you can use OR and AND NOT.

Et voilà! You are now a SQL CONTAINS master!

For more use cases, take a look at the “Examples” section on the official documentation.

Conclusion

In this guide to using CONTAINS in SQL Server, you learned what this function is and how it works. This powerful full-text search function helps you search for strings in text columns. Thanks to the many real-world examples shown above, you know how to use it in different scenarios.

Full-text searching is tricky! To better understand how to use the SQL CONTAINS function, you need a tool to visualize the data in the database. This is precisely the purpose of a powerful database client such as DbVisualizer! In addition to presenting data visually, it supports several DBMS technologies, offers advanced query optimization capabilities, and can generate ERD-like schemas. Try DbVisualizer for free today!

Appendix: Conditions Required by CONTAINS in SQL Server

  1. The CONTAINS function works on columns of the following types: char, varchar, nchar, nvarchar, text, ntext, image, xml, varbinary, and varbinary(max).
  2. The columns in the CONTAINS clause must come from a single table and be full-text indexed.

FAQ

What is the difference between LIKE vs CONTAINS in SQL Server?

The LIKE vs CONTAINS SQL Server comparison boils down to a few facts:

  • LIKE is a pattern-matching operator used for simple string matching, while CONTAINS is used for more advanced full-text searching.
  • LIKE performs partial matching with wildcards, whereas CONTAINS considers word forms, synonyms, and allows weighted searches.
  • CONTAINS requires a full-text index, while LIKE does not.
  • LIKE is typically case-insensitive, while CONTAINS behavior depends on column collation.

Can I apply the SQL Server CONTAINS function on multiple columns?

Yes, you can use the CONTAINS function on multiple columns by concatenating them with commas in the search condition as in the example below:

Copy
        
1 SELECT * 2 FROM <TableName> 3 WHERE CONTAINS((<Column1>, ..., <ColumnN>), '<search_condition>');

How to check that a SQL string contains a substring?

To check if a SQL string contains a substring, you can use the CHARINDEX function as follows:

Copy
        
1 SELECT * 2 FROM <TableName> 3 WHERE CHARINDEX('<substring>', <ColumnName>) > 0;

This query returns rows where the specified substring is found within the specified column. Thus, you do not need CONTAINS to achieve the desired result.

Does the CONTAINS SQL Server function work on columns that do not have a full-text index?

No, the CONTAINS SQL Server function requires all columns mentioned in the predicate to have a full-text index. Otherwise, the query will fail with the following error:

[Code: 7601, SQL State: S1000] Cannot use a CONTAINS or FREETEXT predicate on table or indexed view '<YourTable>' because it is not full-text indexed.

What is the full syntax of the SQL CONTAINS function?

You can find the full syntax of the  SQL CONTAINS on the documentation. To view the Transact-SQL syntax of that function for SQL Server 2014 and earlier versions, explore the documentation of the previous versions.

Dbvis download link img
About the author
Antonello Zanini

Antonello is a software engineer, and often refers to himself as a technology bishop. His mission is to spread knowledge through writing.

The Table Icon
Sign up to receive The Table's roundup
More from the table
Title Author Tags Length Published
title

SQL DDL: The Definitive Guide on Data Definition Language

author Antonello Zanini tags DDL SQL 7 min 2024-02-15
title

SQL DISTINCT: A Comprehensive Guide

author Bonnie tags DISTINCT POSTGRESQL SQL 5 MINS 2024-01-11
title

SQL Interview Questions – SQL Queries

author Lukas Vileikis tags SQL 6 MINS 2024-01-08
title

The SQL BETWEEN Clause Explained

author Lukas Vileikis tags BETWEEN SQL 6 MINS 2023-12-28
title

SQL vs NoSQL Databases: Which is Better?

author Ochuko Onojakpor tags NOSQL SQL 16 MINS 2023-11-20
title

SQL Cheat Sheet: A Comprehensive Guide to SQL Commands and Queries

author Bonnie tags CHEAT SHEET SQL 18 MINS 2023-11-15
title

A Beginners Guide to SQL Window Functions

author Ochuko Onojakpor tags FUNCTIONS SQL 6 MINS 2023-08-18
title

How to Pronounce SQL: SQL or Sequel?

author TheTable tags SQL 3 MINS 2023-05-23
title

Parameterized Queries in SQL – A Guide

author Lukas Vileikis tags SECURITY SQL 5 MINS 2023-05-12

The content provided on dbvis.com/thetable, including but not limited to code and examples, is intended for educational and informational purposes only. We do not make any warranties or representations of any kind. Read more here.

Cookie policy

We use cookies to ensure that we give you the best experience on our website. However you can change your cookie settings at any time in your browser settings. Please find our cookie policy here ↗