Find word in SQL Server Varchar variable

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'
    PRINT 'Not Found'

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

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'
	PRINT 'Goof Up!'


Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.