How Do I Check If a String Contains a Word in SQL Server?

To determine if a string contains a specific word in SQL Server, there are several methods available that cater to different needs. Whether you need a simple check for a substring, case-insensitive matching, or pattern recognition with wildcards, SQL Server provides functions and operators to help achieve this.

This article will provide you with effective SQL Server functions and techniques to accomplish this task.

How to Find a Word in a String in SQL Server?

In this section, we’ll explore the various methods and functions available in SQL Server to identify words within strings. 

Using the LIKE Operator

The LIKE operator is a versatile tool in SQL Server that allows for pattern matching within strings. It’s particularly useful for checking if a string contains a specific sequence of characters. The operator uses wildcards, such as % for any sequence of characters and _ for a single character, to define these patterns.

To check if a string contains a word using LIKE, the % wildcard is typically employed at both the beginning and end of the word. This way, SQL Server searches for the word anywhere within the string.

SELECT 
    CASE 
        WHEN 'SQL Server is powerful' LIKE '%powerful%' THEN 'Contains the word'
        ELSE 'Does not contain the word'
    END AS Result;

In this example, the LIKE operator searches for the word “powerful” within the string “SQL Server is powerful.” If the word is found, it returns “Contains the word.” The % wildcard allows for any characters to precede or follow the word, making the search flexible.

Using the CHARINDEX Function

CHARINDEX is a function in SQL Server that returns the starting position of a specified substring within another string. If the substring is not found, it returns 0. This function is case-insensitive and straightforward for checking the presence of a word.

To use CHARINDEX, you specify the word to search for and the string in which to search. If the function returns a value greater than 0, the word exists in the string.

SELECT 
    CASE 
        WHEN CHARINDEX('powerful', 'SQL Server is powerful') > 0 THEN 'Contains the word'
        ELSE 'Does not contain the word'
    END AS Result;

Here, CHARINDEX searches for the word “powerful” in the string “SQL Server is powerful.” If found, it returns a position greater than 0, leading the CASE statement to conclude that the word is present.

Using the PATINDEX Function

PATINDEX is similar to CHARINDEX but provides pattern matching capabilities like the LIKE operator. It returns the starting position of the first occurrence of a pattern in a string or zero if the pattern is not found. This function is particularly useful when you need to perform wildcard searches within a string.

When using PATINDEX, you define a pattern with wildcards to search for within the string. The function will return the position where this pattern begins.

SELECT 
    CASE 
        WHEN PATINDEX('%powerful%', 'SQL Server is powerful') > 0 THEN 'Contains the word'
        ELSE 'Does not contain the word'
    END AS Result;

In this example, PATINDEX searches for any occurrence of the word “powerful” within the string “SQL Server is powerful.” The % wildcard allows any characters to surround “powerful,” making the function flexible for more complex search patterns.

Using Full-Text Search

For more advanced searches involving natural language processing or multiple words, SQL Server’s Full-Text Search feature proves useful. Full-Text Search enables indexing of character-based columns and allows for searching specific words or phrases. It’s ideal for scenarios involving large volumes of text or complex search requirements.

To use Full-Text Search, you first need to create a Full-Text index on the column you want to search. Then, you can use the CONTAINS function to determine if a specific word exists in the indexed column.

SELECT 
   CASE 
        WHEN CONTAINS(column_name, 'powerful') THEN 'Contains the word'
        ELSE 'Does not contain the word'
    END AS Result
FROM table_name;

Here, CONTAINS checks for the word “powerful” within the specified column. It returns “Contains the word” if the word is found in any of the column’s entries.

Frequently Asked Questions

How to check string contains substring in SQL Server?

The `LIKE` operator in SQL searches for patterns in strings. Use `%` for any number of characters and `_` for a single character. For example, `LIKE ‘%apple%’` finds “apple” anywhere, and `LIKE ‘_pple’` finds strings starting with a single character followed by “pple”.

How to check if string contains number in SQL?

The `ISNUMERIC()` function in SQL Server determines if a string contains only digits. It returns `TRUE` if the string is a number, and `FALSE` otherwise.

Conclusion 

Whether you’re dealing with customer reviews, product descriptions, or any other textual data, knowing how to search for specific words can be invaluable for analysis, filtering, and data extraction. Remember to experiment with different approaches and consider the specific requirements of your use case.