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)='Kenneth James    ' 
    ---Check here @mainString contains Kenneth or not, if it contains then retrun greater than 0 then print Find otherwise Not Find 
    if CHARINDEX('Kenneth',@mainString) > 0  
    begin 
       select 'Find' As Result 
    end 
    else 
        select 'Not Find' As Result 

Output

CHARINDEX
 
Method 2 - Using LIKE Predicate
    DECLARE @WholeString VARCHAR(50) 
    DECLARE  @ExpressionToFind VARCHAR(50) 
    SET @WholeString = 'Kenneth James' 
    SET @ExpressionToFind = 'James' 
      
    IF @WholeString LIKE '%' + @ExpressionToFind + '%' 
        PRINT 'Yes it is find' 
    ELSE 
        PRINT 'It doesn''t find' 

HostForLIFE.eu SQL Server 2016 Hosting
HostForLIFE.eu is European Windows Hosting Provider which focuses on Windows Platform only. We deliver on-demand hosting solutions including Shared hosting, Reseller Hosting, Cloud Hosting, Dedicated Servers, and IT as a Service for companies of all sizes.