How To Check If A String Contains A Substring In SQL Server

In this blog, I will explain how to check a specific word or character in a given statement using CHARINDEX() function in SQL Server. An alternative to CHARINDEX() is using LIKE predicate.

SQL contains string - In this blog, I will explain how to check a specific word or character in a given statement in SQL Server, using CHARINDEX function or SQL Server and check if the string contains a specific substring with CHARINDEX function.

An alternative to CHARINDEX() is using LIKE predicate.

Method 1 - Using CHARINDEX() function

CHARINDEX()

This function is used to search for a specific word or a substring in an overall string and returns its starting position of match. In case no word is found, then it will return 0 (zero).

Let us understand this with examples.

Syntax

CHARINDEX (SearchString, WholeString, [start_location] )

• SearchString is the substring that you want to find the position of.
• WholeString is the string that you want to search in.
• start_location is the optional parameter that specifies the starting position of the search. If this parameter is not specified, the search will start at the beginning of the string.

The CHARINDEX() function returns the position of the substring in the string, or 0 if the substring is not found. The position is returned as a 1-based integer, meaning that the first character in the string is at position 1.

For example, the following query will find the position of the substring "bar" in the string "foobarbaz":

SELECT CHARINDEX("bar", "foobarbaz")

This query will return the value 3, which is the position of the first occurrence of the substring "bar" in the string "foobarbaz".

The CHARINDEX() function can also be used to perform case-sensitive searches. To do this, you can use the COLLATE keyword to specify the collation that you want to use for the search. For example, the following query will perform a case-sensitive search for the substring "Bar" in the string "foobarbaz":

SELECT CHARINDEX("Bar", "foobarbaz", COLLATE Latin1_General_CI_AS)

This query will return the value 0, because the substring "Bar" is not found in the string "foobarbaz" when a case-sensitive search is performed.

The CHARINDEX() function is a useful tool for finding the position of a substring in a string. It can be used to perform both case-sensitive and case-insensitive searches.

Example

Declare @mainString nvarchar(100)='Amit Kumar Yadav'  
---Check here @mainString contains Amit or not, if it contains then retrun greater than 0 then print Find otherwise Not Find  
if CHARINDEX('Amit',@mainString) > 0   
begin  
   select 'Find' As Result  
end  
else  
    select 'Not Find' As Result 

Output

Method 2 - Using LIKE Predicate

The LIKE predicate operator can be used to find a substring into a string or content. The LIKE operator combined with % and _ (underscore) is used to look for one more characters and a single character respectively. You can use % operator to find a sub-string. 

In the following SQL query, we will look for a substring, 'Kumar" in the string.  

DECLARE @WholeString VARCHAR(50)  
DECLARE  @ExpressionToFind VARCHAR(50)  
SET @WholeString = 'Amit Kumar Yadav'  
SET @ExpressionToFind = 'Kumar'  
   
IF @WholeString LIKE '%' + @ExpressionToFind + '%'  
    PRINT 'Yes it is find'  
ELSE  
    PRINT 'It doesn''t find'  

Output

This method can also be used in the WHERE clause of SELECT, UPDATE, and DELETE statements. The following SELECT satement selects records from Employees table of Northwind database where Employee's Title contains a substring, 'Sales'. 

SELECT [EmployeeID]
,[LastName]
,[FirstName]
,[Title]
FROM [NORTHWND].[dbo].[Employees]
WHERE Title LIKE '%Sales%'

The output of the above query returns the following results. 

Summary

In this blog, we saw how to get data with substrings in a column.