How to Check if a String Contains a Substring in it in 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:

10 thoughts on “How to Check if a String Contains a Substring in it in Sql Server

  1. I enjoyed reading this article

    Thanks for educating the community.

    Please keep publish more and more articles

    Thanks a lot

  2. 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

Leave a Reply

Your email address will not be published. Required fields are marked *