SQL Server Hosting – HostForLIFE.eu :: Check If A String Contains A Substring In SQL Server

Peter Cole | May 4, 2017 | Other Related Posts | No Comments

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

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

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

Method 2

Using LIKE Predicate

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'

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

Related Posts

About The Author

Pete