In this article I wil explain how to check a specific word or characters in a given statement in SQL Server using CHARINDEX function or SQL Server check if string contains specific substring with CHARINDEX function.
Alternative to CHARINDEX() is using the LIKE predicate
Method 1: Using CHARINDEX() function  
CHARINDEX(): This function is used to search for specific word or substring in overall string and returns its starting position of match In case if no word 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  
How to Check if a String Contains a Substring in Sql Server 
 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'  
Output : 
How to Check if a String Contains a Substring in Sql Server
  Modified On Nov-29-2017 08:03:17 PM

Leave Comment