SQL CONTAINS Explained [Practical examples]


SQL

Reviewer: Deepak Prasad

Introduction

Definition and Purpose of SQL CONTAINS

SQL CONTAINS is a predicate used in the SQL query language that facilitates a condition for Full-Text Search in database tables. It allows for sophisticated searching within text-type columns of a database where a full-text index has been applied. By using CONTAINS, users can search for specific words, phrases, or various forms of a specific word within text-based fields in a database. This is instrumental in conducting detailed and nuanced searches, primarily when dealing with large volumes of text data or when needing to pinpoint specific information within text fields.

Overview of Full-Text Search

Full-Text Search is a technique used in databases that allows for more complex and flexible querying of text data compared to the traditional SQL LIKE operator. Full-Text Search is designed to perform efficiently in searching tasks such as finding specific words or phrases within text fields, as well as more advanced search operations like proximity searches and wildcard searches.

 

Basic Syntax of SQL CONTAINS

The CONTAINS clause in SQL is structured to search within full-text indexed columns in a database table for specific words or phrases. The basic syntax of the CONTAINS clause is as follows:

CONTAINS 
( 
    { column_name | ( column_list ) | * }, 
    '<contains_search_condition>' 
    [ 
        { <AND> | <AND NOT> | <OR> } 
        <contains_search_condition> 
        [ ...n ] 
    ]
);

Here:

  • column_name: It is the name of a full-text indexed column of the table specified in the FROM clause, The datatype pf columns can be char, varchar, nchar, nvarchar, text, ntext, image, XML, varbinary, or varbinary(max)
  • column_list: It specifies two or more columns, separated by commas, column_list must be enclosed in parentheses,
  • *: It specifies that the query searches all full-text indexed columns in the table specified in the FROM clause for the given search condition. The columns in the CONTAINS clause must come from a single table that has a full-text index.
  • <contains_search_condition>: It is a nvarchar type expression, if any other type of value is specified it will automatically convert it to nvarchar, it specifies the text to search for in column_name and the conditions for a match,
  • { AND | & } | { AND NOT | &! } | { OR | | }: It specifies a logical operation between two contains search conditions
  • { AND | & }: The logical AND operator Indicates that the two contains search conditions must be met for a match. The ampersand symbol (&) may be used instead of the AND keyword to represent the AND operator.
  • { AND NOT | &! }: The logical AND NOT operator indicates that the second search condition must not be present for a match. The ampersand followed by the exclamation mark symbol (&!) may be used instead of the AND NOT keyword to represent the AND NOT operator
  • { OR | | }: The logical OR operator Indicates that either of the two contains search conditions must be met for a match. The bar symbol (|) may be used instead of the OR keyword to represent the OR operator

An example within a SELECT statement might look like this:

SELECT column1, column2
FROM table_name
WHERE CONTAINS ( column_name, 'search_term' );

Common Parameters and Arguments

In the CONTAINS clause, several parameters and arguments can be utilized to customize the search query further:

  • Column Name: Specifies the column where the full-text search will be performed. Multiple columns can be included using OR.
  • Search Condition: Consists of one or several words or phrases to search for, and can include:
    • Simple Term: A direct word or phrase to be searched.
    • Prefix Term: Allows for searching words that have common prefixes.
    • Generation Term: Utilized for inflectional forms of a specific word.
    • Proximity Term: Helps in finding words or phrases close to another word or phrase.
    • Thesaurus Term: Used for synonyms based on the thesaurus of Full-Text Search.
  • Boolean Operators: Operators such as AND, OR, and NOT can be used to combine multiple conditions for more refined search results.

 

Set up Lab Environment

Below is a SQL command to create a sample table named Articles which we can use for demonstrations throughout an article about SQL CONTAINS.

CREATE TABLE Articles (
    ArticleID INT PRIMARY KEY,
    Title TEXT,
    Content TEXT
);

INSERT INTO Articles (ArticleID, Title, Content) VALUES
(1, 'Introduction to SQL', 'SQL, or Structured Query Language, is a domain-specific language used in programming and managing relational databases.'),
(2, 'Understanding SQL CONTAINS', 'The SQL CONTAINS function is used for full-text searches in SQL Server databases.'),
(3, 'Advanced SQL Techniques', 'Dive deep into advanced SQL techniques, including stored procedures, triggers, and advanced querying using CONTAINS.'),
(4, 'Optimizing SQL Queries', 'Learn how to optimize your SQL queries to improve performance and create more efficient databases.'),
(5, 'SQL Security Best Practices', 'Explore best practices for securing your SQL databases and protecting data integrity.');

Output:

ArticleID Title Content
1 Introduction to SQL SQL, or Structured Query Language, is a domain-specific language used in programming and managing relational databases.
2 Understanding SQL CONTAINS The SQL CONTAINS function is used for full-text searches in SQL Server databases.
3 Advanced SQL Techniques Dive deep into advanced SQL techniques, including stored procedures, triggers, and advanced querying using CONTAINS.
4 Optimizing SQL Queries Learn how to optimize your SQL queries to improve performance and create more efficient databases.
5 SQL Security Best Practices Explore best practices for securing your SQL databases and protecting data integrity.

 

Implementing SQL CONTAINS in Queries

Below are practical examples illustrating the use of the CONTAINS clause in queries using the Articles table previously created. These examples assume that a full-text index has been created on the Title and Content columns.

 

1. Using CONTAINS in SELECT Statements

Finding articles where the Content contains the word "SQL".

SELECT ArticleID, Title 
FROM Articles
WHERE CONTAINS (Content, 'SQL');

Expected Output

SQL CONTAINS Explained [Practical examples]

 

2. Combining CONTAINS with Other SQL Clauses

Finding articles where the Content contains the word "SQL", and the Title contains the word "Advanced".

SELECT ArticleID, Title 
FROM Articles
WHERE CONTAINS (Content, 'SQL') AND CONTAINS (Title, 'Advanced');

Expected Output

SQL CONTAINS Explained [Practical examples]

 

Understanding the Importance of the Full-Text Index

The Full-Text Index is pivotal for enhancing the performance of full-text queries by indexing the significant words or tokens found in the columns specified, enabling swift and efficient text searches using predicates like CONTAINS.

 

Creating a Full-Text Index

To work with the CONTAINS predicate, you need to create a Full-Text Index on the columns you want to search. Below is an example of creating a Full-Text Index on the Articles table:

Creating a Full-Text Catalog

CREATE FULLTEXT CATALOG articles_catalog AS DEFAULT;

Creating a Full-Text Index on the Articles Table

CREATE FULLTEXT INDEX ON Articles
    (Title LANGUAGE English, Content LANGUAGE English) 
KEY INDEX PK_Articles 
ON articles_catalog;

Here, PK_Articles is assumed to be the primary key index on the Articles table. Adjust as per your actual primary key index name.

 

Managing and Maintaining Full-Text Indexes

Maintenance of the Full-Text Index, such as updating or deleting it, is also essential. Here’s how you can do it:

Updating the Full-Text Index

ALTER FULLTEXT INDEX ON Articles START UPDATE POPULATION;

Deleting the Full-Text Index

DROP FULLTEXT INDEX ON Articles;

These commands help in managing the Full-Text Index, ensuring it stays up-to-date and relevant, thereby maintaining the efficiency of the full-text searches.

 

Using Wildcards and Proximity Terms in CONTAINS

Basics of Wildcards

Wildcards allow you to search for words based on partial information. You can use a wildcard to replace zero or more characters in a word of a search string.

Example: Finding articles that have words starting with "opti" in the title.

SELECT ArticleID, Title
FROM Articles
WHERE CONTAINS (Title, ' "opti*" ');

Expected Output

SQL CONTAINS Explained [Practical examples]

 

Proximity Term Usage in Queries

Proximity terms in CONTAINS help in finding rows where specified words or phrases are near each other.

Example: Finding articles where the words "SQL" and "techniques" are close to each other in the content.

SELECT ArticleID, Title
FROM Articles
WHERE CONTAINS (Content, ' NEAR((SQL, techniques), 5) ');

Expected Output

SQL CONTAINS Explained [Practical examples]

In this example, the query retrieves articles where the words "SQL" and "techniques" appear within five words of each other.

 

Formulating Complex CONTAINS Queries

Creating advanced queries using the CONTAINS predicate involves the use of Boolean operators and prioritizing terms to fine-tune the search process, making it more flexible and powerful.

 

Using Boolean Operators in CONTAINS Queries

Boolean operators such as AND, OR, and NOT can be used to combine or exclude words or phrases in search conditions.

Example: Finding articles that contain either the word "SQL" or "techniques", but not "security".

SELECT ArticleID, Title
FROM Articles
WHERE CONTAINS(Content, 'SQL OR techniques NOT security');

Expected Output

SQL CONTAINS Explained [Practical examples]

 

Prioritizing Terms in CONTAINS Queries

Weighting different terms can help in retrieving more relevant results by prioritizing certain words or phrases in the search.

Example: In SQL Server, you might use the ISABOUT predicate to weight terms.

SELECT ArticleID, Title
FROM Articles
WHERE CONTAINS(Content, 
    'ISABOUT (SQL weight (.8), techniques weight (.3), security weight (.1))');

Results will be prioritized based on the weighting of terms, showing rows containing "SQL" as more relevant.

 

Real World Practical Examples

1. Search for a Single Word in Multiple Columns

Finding articles where the word "SQL" appears in either the Title or Content columns.

SELECT ArticleID, Title 
FROM Articles
WHERE CONTAINS ((Title, Content), 'SQL');

Expected Output:

ArticleID Title
1 Introduction to SQL
2 Understanding SQL CONTAINS
3 Advanced SQL Techniques
4 Optimizing SQL Queries
5 SQL Security Best Practices

2. Search for a Word in All Full-Text Indexed Columns

If you have a Full-Text Index covering multiple columns, you can search all indexed columns.

SELECT ArticleID, Title 
FROM Articles
WHERE CONTAINS (*, 'SQL');

Expected Output:

ArticleID Title
5 SQL Security Best Practices

3. Search for Two Conditions with Logical OR

Finding articles that contain either "SQL" or "security" in the content.

SELECT ArticleID, Title 
FROM Articles
WHERE CONTAINS (Content, 'SQL OR security');

Expected Output:

ArticleID Title
1 Introduction to SQL
2 Understanding SQL CONTAINS
3 Advanced SQL Techniques
4 Optimizing SQL Queries
5 SQL Security Best Practices

4. Search for Two Conditions with Logical AND

Finding articles that contain both "SQL" and "techniques" in the content.

SELECT ArticleID, Title 
FROM Articles
WHERE CONTAINS (Content, 'SQL AND techniques');

Expected Output:

ArticleID Title
3 Advanced SQL Techniques

5. Search for Two Conditions with Logical AND NOT

Finding articles that contain "SQL" but not "security" in the content.

SELECT ArticleID, Title 
FROM Articles
WHERE CONTAINS (Content, 'SQL AND NOT security');

Expected Output:

ArticleID Title
1 Introduction to SQL
2 Understanding SQL CONTAINS
3 Advanced SQL Techniques
4 Optimizing SQL Queries

6. SQL CONTAINS with NEAR | ~

Finding articles where the words "SQL" and "techniques" are near each other in the content.

SELECT ArticleID, Title 
FROM Articles
WHERE CONTAINS (Content, 'NEAR((SQL, techniques), 5)');

Or, using the tilde ~ for proximity search in some versions of SQL.

SELECT ArticleID, Title 
FROM Articles
WHERE CONTAINS (Content, '"SQL" ~ "techniques"');

Expected Output:

ArticleID Title
3 Advanced SQL Techniques

 

Frequently Asked Questions

How to use contains in SQL?

CONTAINS is used in SQL to perform full-text search in a column. It searches for the specified words or phrases within a targeted column. For instance, to find rows in an Articles table where the Content column contains the word "SQL", you would use a query like: SELECT * FROM Articles WHERE CONTAINS(Content, 'SQL');

What is the contains formula in SQL?

The CONTAINS formula in SQL involves the keyword CONTAINS, the targeted column name, and the word or phrase to search for. For example: SELECT * FROM table_name WHERE CONTAINS(column_name, 'keyword');

What is like %% in SQL?

The LIKE operator in SQL is used in a WHERE clause to search for a specified pattern in a column. % is a wildcard character that matches zero or more characters. For example: SELECT * FROM table_name WHERE column_name LIKE '%keyword%';

How do you search within a string in SQL?

To search within a string in SQL, you can use the LIKE operator or the CONTAINS function (for full-text search). With LIKE, you can use wildcard characters to match patterns within the string.

Is there a contains function in SQL?

Yes, the CONTAINS function is available in SQL, specifically in databases that support full-text indexing, like SQL Server. It is used to search for specific words or phrases within text-type columns.

How do I search for a specific string?

To search for a specific string in SQL, you can use the LIKE operator or CONTAINS function. Using LIKE, you can find rows that match a specified pattern in a column.

How do you search if a string contains a string?

You can use the LIKE operator or the CONTAINS function. With LIKE, the % wildcard can be used to match any sequence of characters.

How does SQL CONTAINS differ from the LIKE operator?

While both are used for pattern matching, CONTAINS is specifically designed for full-text searches, and it is more powerful and flexible than the LIKE operator, especially for extensive text data.

Can SQL CONTAINS handle partial word searches?

CONTAINS doesn’t handle partial matches directly. It primarily works with prefixes. For more flexible partial matching, consider using FREETEXT or other approaches like the LIKE operator.

How does the NEAR operator work in SQL CONTAINS?

The NEAR operator within CONTAINS is used to find rows where the specified words or phrases are near each other in the text column.

 

SQL CONTAINS vs FREETEXT vs LIKE

Below is a comparative table that illustrates the differences between the CONTAINS function, FREETEXT, and the LIKE operator in SQL.

Feature CONTAINS FREETEXT LIKE
Search Type Full-Text Search Full-Text Search Pattern Matching
Index Requirement Requires Full-Text Index Requires Full-Text Index No Index Requirement
Precision High Precision Moderate Precision Low Precision
Flexibility High (with operators) Moderate Low
Boolean Operators Supports (AND, OR, NOT) Not Supported Explicitly Can be used in WHERE clause
Proximity Searches Supports (NEAR) Not Supported Explicitly Not Supported
Wildcard Searches Supports Prefix Terms Not Directly Supported Supports (%, _)
Natural Language Support Partial Support Strong Support Not Supported
Performance on Large Text Optimized Optimized Less Optimized
Use Case Complex, Precise Queries General, Natural Language Queries Simple Pattern Matches

In this table:

  • Search Type: Defines what kind of search each method specializes in.
  • Index Requirement: Indicates whether a full-text index is required.
  • Precision: Indicates the accuracy and exactness of the search results.
  • Flexibility: Describes how easily the search query can be modified or complexified.
  • Boolean Operators: Shows the ability to use logical operators like AND, OR, NOT.
  • Proximity Searches: Capability of searching words or phrases close to each other.
  • Wildcard Searches: Ability to use wildcard characters for flexible matching.
  • Natural Language Support: Ability to understand and work with natural language queries.
  • Performance on Large Text: Indicates how well the method performs on extensive text or documents.
  • Use Case: Suggests scenarios where each method might be preferable or suitable.

 

Conclusion

  • Understanding SQL CONTAINS: It's vital to understand that CONTAINS is primarily used for full-text searches within SQL, helping in finding specific words or phrases within text columns.
  • Flexibility and Precision: CONTAINS offers a high level of precision and flexibility, allowing for the use of various operators such as Boolean operators and proximity terms.
  • Comparison with Other Methods: When compared to other full-text search methods like FREETEXT and pattern matching methods like LIKE, CONTAINS stands out for its precision and powerful querying capabilities.
  • Application in Real-world Scenarios: Knowing when to use CONTAINS, as opposed to other methods, is crucial. It is especially powerful when dealing with extensive text searches in larger databases.
  • Continuous Learning and Practice: As with any SQL function or command, continuous learning and practical application are key to mastering the use of CONTAINS for full-text searches.

Links to Official Documentation for Further Reading

 

Views: 311
Falguni Thakker

Falguni Thakker

She is a dedicated professional with expertise in SQL, Python, C++, and Linux. Currently serving as a professor at a prestigious university. With a passion for teaching and a strong technical background, she inspires the next generation of computer scientists. You can connect with her on her LinkedIn profile.

Can't find what you're searching for? Let us assist you.

Enter your query below, and we'll provide instant results tailored to your needs.

If my articles on GoLinuxCloud has helped you, kindly consider buying me a coffee as a token of appreciation.

Buy GoLinuxCloud a Coffee

For any other feedbacks or questions you can send mail to admin@golinuxcloud.com

Thank You for your support!!

Leave a Comment