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.
Syntax:
- CHARINDEX ( SearchString,WholeString[ , startlocation ] )
- 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'
Output :
Leave Comment