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'

RESULT:

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'

RESULT:

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?