intro
Explore the power of the CONTAINS SQL Server function and understand how it works with real-world examples.
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:
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:
1
SELECT *
2
FROM <TableName>
3
WHERE CONTAINS (<ColumnName> | ( <ColumnList> ) | *, '<SearchCondition>')
The arguments supported by the function are:
Consider the CONTAINS SQL 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, it is case-insensitive in this case:
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:
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!
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:
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:
1
SELECT Id, Name, Description
2
FROM Product
3
WHERE ListPrice > 20 AND CONTAINS(Description, 'powerful');
Search for a Phrase
The SQL CONTAINS example below returns all products that contain the “with high-” phrase:
1
SELECT Id, Name, Description
2
FROM Product
3
WHERE CONTAINS(Description, '"with high-"');
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:
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*"');
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”):
1
SELECT Id, Name, Description
2
FROM Product
3
WHERE CONTAINS(Description, 'NEAR((headphones, technology), 5)');
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.
1
SELECT Id, Name, Description
2
FROM Product
3
WHERE CONTAINS(Description, 'FORMSOF (INFLECTIONAL, game)');
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:”
1
SELECT Id, Name, Description
2
FROM Product
3
WHERE CONTAINS(Description, 'high AND powerful');
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
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:
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>), '<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:
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.
