Check A String Contains Substring In SQL Server

In this article, I will explain how to check a string contains a substring  or not in SQL Server. 

There are two ways for check it. They are as given below.

1. CHARINDEX() 

2. LIKE Predicate

3. PATINDEX function

Now start here one by one with practically.

1. CHARINDEX() : 

This function is used to search a specific word or a substring in whole string if it find out then returns it’s starting position. If it is found, then it will return 0 (zero).

Let us understand this with examples.

Syntax

              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 'Found' As Result  
6. end  
7. else  
8.     select 'Not Found' As Result  

Output :

Using CHARINDEX

2. LIKE Predicate : 

        Any where in the given string.

Syntax :

               LIKE '%' + Search Expression +'%'                      

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 'Found'  
8. ELSE  
9.     PRINT 'Not Found' 

Output :

Like Predicate

3. PATINDEX():  
                          
This function returns starting location of a pattern from given string.

Syntax:
             PATINDEX('%Pattern%', yourString)

Example: 

1. DECLARE @WholeString VARCHAR(50)  
2. DECLARE  @ExpressionToFind VARCHAR(50)  
3. SET @WholeString = 'dotnettechpoint'  
4. SET @ExpressionToFind = 'dot'
5. SELECT PATINDEX ('%'+@ExpressionToFind+'%',@WholeString) AS RESULT  
6. 
   

Output : 

In above snapshot you can see PATINDEX() returns starting matching Index of substring from a string. It means expressionToFind is available in given string.