We can use the CHARINDEX() function to check whether a String contains a Substring in it. Name of this function is little confusing as name sounds something to do with character, but it basically returns the starting position of matched Substring in the main String. If it is not found then this function returns value 0.
Alternative to CHARINDEX() is using the LIKE predicate. Example 2 Demonstrates this.
Let us understand this with examples
Example 1: Using CHARINDEX() function
DECLARE @ExpressionToSearch VARCHAR(50) SET @ExpressionToSearch = 'Basavaraj Prabhu Biradar' --Check whether @ExpressionToSearch contains the substring --'Prabhu' in it IF CHARINDEX('Prabhu', @ExpressionToSearch ) > 0 PRINT 'Yes it Contains' ELSE PRINT 'It doesn''t Contain'
Example 2: Using LIKE Predicate
DECLARE @ExpressionToSearch VARCHAR(50) DECLARE @ExpressionToFind VARCHAR(50) SET @ExpressionToSearch = 'Basavaraj Prabhu Biradar' SET @ExpressionToFind = 'Prabhu' IF @ExpressionToSearch LIKE '%' + @ExpressionToFind + '%' PRINT 'Yes it Contains' ELSE PRINT 'It doesn''t Contain'
You may like to read the other popular articles:
- How to get Date Part only from DateTime in Sql Server
- Difference Between Sql Server VARCHAR and NVARCHAR Data Type
- How to Insert Stored Procedure result into a table in Sql Server?
- Difference Between Sql Server VARCHAR and VARCHAR(MAX) Data Type
- New Features in Sql Server 2008
- Difference between DateTime and DateTime2 DataType
- Differences Between RAISERROR and THROW in Sql Server
- How to find all the Stored Procedures having a given text in it?
10 thoughts on “How to Check if a String Contains a Substring in it in Sql Server”
Is this case sensitive? If so, is there a possibility to ignore case in CHARINDEX?
I enjoyed reading this article
Thanks for educating the community.
Please keep publish more and more articles
Thanks a lot
Thank you Kris
Hi…. i am getting an error when i try to assign value to a variable inside IF. Tge value I am assigning is a column value
hi,…amazing….thanks a lot sir.
Hi, what about performance, when i check this condition in a cursor, is there a difference ?
Thanks, this was helpful
Thank for Sharing good Information