I’ll be discussing two ways by which you can search for a string in a SQL Server variable:

Using CHARINDEX() Method:

This function is used to return the position of a substring in string. It’ll return 0 if the substring is not found.
This start position value of the word can be useful for other calculations as well.

DECLARE @strWord nvarchar(50)='My Test String'  

IF CHARINDEX('Test String',@strWord) > 0   
   PRINT 'Found'
ELSE  
    PRINT 'Not Found'
	
Output:
Found

Use the Index value in another variable for other calculations with CHARINDEX as below:

DECLARE @iDex INT
SELECT @iDex=CHARINDEX('Test String',@strWord)

Another way is to use the LIKE operator in SQL Server. This operator is also used in SQL queries’ WHERE clause e.g.

SELECT * FROM Tbl_Name WHERE mainStringCol LIKE '%Test String%'

The above query will fetch all rows where the mainStringCol contains the text “Test String”.

Now, to find a string in a Varchar variable using the LIKE operator, e.g. below:

DECLARE @txtMailBody nvarchar(MAX)='  Hello Mr Anderson...'
IF @txtMailBody LIKE '%Hello Mr%'
	PRINT 'Hello'
ELSE IF @txtMailBody LIKE '%Wad Up%'
	PRINT 'Wad Up'
ELSE
	PRINT 'Goof Up!'

Output:
Hello