intro
Explore the power of SQL CONTAINS function and understand how this function 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. The SQL CONTAINS function servers exactly that role, offering a powerful tool designed for full-text search queries.
In this article, you will dig into the world of the CONTAINS SQL Server function. 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 SQL CONTAINS Function in SQL Server?
CONTAINS is an SQL Server function to search for a word or phrase in one or more text columns using precise or fuzzy matching. Specifically,CONTAINS in SQL Server 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 CONTAINS SQL Server function can search for:
How to Use CONTAINS in SQL Server
Below is the T-SQL CONTAINS syntax in a sample query:
1
SELECT *
2
FROM TableName
3
WHERE CONTAINS (ColumnName | ( ColumnList ) | *, 'SearchCondition')
The arguments supported by the function are:
Note: The first three arguments are connected by a logical OR, so the SQL CONTAINS function can accept as the first argument either a column name, a comma-separated list of columns, or *.
Your First SQL CONTAINS Example
Consider the CONTAINS SQL Server example query below:
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, the result set involves the target keyword regardless of its case:
If Name and Description columns were not full-text indexed, the query would fail with:
1
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. In detail, you would have to launch a query as below:
1
CREATE FULLTEXT CATALOG ProductCatalog AS DEFAULT;
2
3
CREATE FULLTEXT INDEX ON Product(Name, Description)
4
KEY INDEX PK__Product__3214EC07303CD7E1;
Unfortunately, the full-text search component is optional in SQL Server. Thus, your FULLTEXT index creation query may fail with:
1
[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, feel free to skip 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:
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:
Keep clicking the “Next” button until you reach the following screen:
Click “Next” again and select the second option as follows:
Uncheck the “Azure Extension for SQL Server” option and press the “Next” button:
Check the “Full-Text and Semantic Extractions for Search” component, click “Next,” and wait for the installation process to end.
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!
CONTAINS SQL Server Use Cases
Learn how to use CONTAINS in SQL Server by exploring the most common possible scenarios through real-world examples.
Note: The SQL CONTAINS queries below will be executed in DbVisualizer, a fully-featured, powerful, multi-database client with the highest user satisfaction in the market
In particular, the queries will be run on the following Product table:
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 40 units of value that contain the word “wireless” in their description:
1
SELECT Id, Name, Description
2
FROM Product
3
WHERE Price > 40 AND CONTAINS(Description, 'wireless');
Execute the query, and you will get the following result:
Search for a Phrase
The SQL CONTAINS example below returns all products that contain the “designed for” phrase:
1
SELECT Id, Name, Description
2
FROM Product
3
WHERE CONTAINS(Description, '"designed for"');
The result of the above CONTAINS SQL Server query will be:
Note the use of the double quotes. Without them the query will fail with:
1
Syntax error near 'for' in the full-text search condition 'designed for-'.
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:
Search for the Prefix of a Word
This query retrieves all products whose name contains at least one word starting with the “W” character:
1
SELECT Id, Name, Description
2
FROM Product
3
WHERE CONTAINS(Description, '"W*"');
The above query that show the usage of wildcards with CONTAINS in SQL Server will produce:
Notice the use of the double quotes and the * character to specify the prefix syntax in the CONTAINS SQL function. The double quotes are required; without them, the query won't fail but will return no results, as the * will be interpreted as a regular asterisk rather than a wildcard:
Word Proximity Search
The following SQL CONTAINS example query searches for all products whose description contains the word ”device” within 5 search terms of the word "smartphone" in the specified order (“device” must precede “smartphone”):
1
SELECT Id, Name, Description
2
FROM Product
3
WHERE CONTAINS(Description, 'NEAR((device , smartphone), 5)');
The resulting dataset will be:
Find out more about the NEAR full-text operator.
Search for Inflectional Forms
This query searches for all products with words of the form work (e.g. worked, working, and so on).
1
SELECT Id, Name, Description
2
FROM Product
3
WHERE CONTAINS(Description, 'FORMSOF (INFLECTIONAL, work)');
The result will be:
Note that the product “Smartwatch” contains the word “working” but not “work.” That is still a match as “working” is an inflectional form of “work.”
Search Through Logical Operators
The CONTAINS predicate in the query below will search for descriptions that contain both the word “comfortable” and the word “long:”
1
SELECT Id, Name, Description
2
FROM Product
3
WHERE CONTAINS(Description, 'comfortable AND long');
The result will be:
Notice the use of the AND operator. Similarly, you can use OR and AND NOT.
Search for Opposite Match
You can use CONTAINS to get all records that do not match some criteria with:
1
SELECT Id, Name, Description
2
FROM Product
3
WHERE NOT CONTAINS(*, 'Laptop');
The above SQL NOT CONTAINS query would produce:
Note that the “Laptop” device has been excluded by the results.
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
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:
Below is a summary table comparing LIKE and CONTAINS in SQL Server:
| Feature | LIKE | CONTAINS |
|---|---|---|
| Type of Matching | Simple string matching | Advanced full-text search |
| Pattern Matching | Uses wildcards (e.g., %, _) for partial matching | Supports word forms, synonyms, and weighted searches |
| Index Requirement | No index required | Requires a full-text index |
| Case Sensitivity | Typically case-insensitive (depends on collation) | Depends on column collation (can be case-sensitive or case-insensitive) |
| Performance | Generally slower for large datasets | Optimized for large text-based datasets with full-text indexing |
| Use Case | Basic searches (e.g., LIKE '%word%') | Complex searches involving words and their variations (e.g., CONTAINS('word')) |
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:
1
SELECT *
2
FROM TableName
3
WHERE CONTAINS((Column1, ..., ColumnN), 'SearchCondition');
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:
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:
1
Cannot use a CONTAINS or FREETEXT predicate on table or indexed view '<YourTable>' because it is not full-text indexed.
What is the full T-SQL CONTAINS syntax?
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.
What is the difference between using AND NOT in the CONTAINS operator and using the SQL NOT CONTAINS?
Using AND NOT with the CONTAINS operator means you are combining two conditions: one where the CONTAINS condition is true, and the other where a term or phrase is excluded. For example, CONTAINS(Name, 'laptop') AND NOT CONTAINS(Description, 'tablet'). On the other hand, the SQL NOT CONTAINS function excludes results based on a full-text search.
