SQL Server Hosting – HostForLIFE.eu :: Check If A String Contains A Substring In SQL Server
|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
1 2 3 4 5 6 7 8 |
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 |
Method 2
Using LIKE Predicate
1 2 3 4 5 6 7 8 9 |
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