Tag Archives: String Contains a Substring in it – Sql Server

How to Check if a String Contains a Substring in it in Sql Server

Scripts, Sql Server, , , ,

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:
Substring_Within_String_Using_CHARINDEX

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:
Substring_Within_String_Using_LIKE

You may like to read the other popular articles: