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'
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
Advertisements

Leave a Reply

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

WordPress.com Logo

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

Google photo

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

Twitter picture

You are commenting using your Twitter 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.