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'
-
- 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