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

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.

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[ , startlocation ] )

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

CHARINDEX 

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

Like Predicate 

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. 

SQL Contains 

Summary

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