In Focus

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

This blog explains how to check, if a string contains a substring in SQL Server or not.

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

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.
  1. CHARINDEX ( SearchString,WholeString[ , startlocation ] )
  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  

Method 2 - Using LIKE Predicate

  1. DECLARE @WholeString VARCHAR(50)  
  2. DECLARE  @ExpressionToFind VARCHAR(50)  
  3. SET @WholeString = 'Amit Kumar Yadav'  
  4. SET @ExpressionToFind = 'Kumar'  
  6. IF @WholeString LIKE '%' + @ExpressionToFind + '%'  
  7.     PRINT 'Yes it is find'  
  8. ELSE  
  9.     PRINT 'It doesn''t find'  
Like Predicate