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

SQL contains string is used to check if a string contains a substring in SQL Server or not.

SQL contains string - In this blog, I wil 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
  1. CHARINDEX ( SearchString,WholeString[ , startlocation ] )
Example
  1. Declare @mainString nvarchar(100)='Amit Kumar Yadav'  
  2. ---Check here @mainString contains Amit or not, if it contains then retrun greater than 0 then print Find otherwise Not Find  
  3. if CHARINDEX('Amit',@mainString) > 0   
  4. begin  
  5.    select 'Find' As Result  
  6. end  
  7. else  
  8.     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 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.  
  1. DECLARE @WholeString VARCHAR(50)  
  2. DECLARE  @ExpressionToFind VARCHAR(50)  
  3. SET @WholeString = 'Amit Kumar Yadav'  
  4. SET @ExpressionToFind = 'Kumar'  
  5.    
  6. IF @WholeString LIKE '%' + @ExpressionToFind + '%'  
  7.     PRINT 'Yes it is find'  
  8. ELSE  
  9.     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.