Basic Logics · Real Scenario Logic's · String Manipulation

Counting Number of Occurrences of a Particular Word inside the String Using T-SQL

Introduction

In this blog post, we will see how can we easily find the number of occurrences of a Particular Word inside the String Using T-SQL.

Check our previous post for Finding Number of Occurrences of a Particular Character in the String.

Finding Number of Occurrences of a Particular Word inside the String

LOGIC

The Difference between the Length of String and Length of String without considering given particular word divided by the Length of Search String gives us the number of occurrences of a particular word inside the string.

SQL SCRIPT

DECLARE @String VARCHAR(100),
		@Search_String VARCHAR(100);

SELECT @String = 'How are you? We are fine.',
	   @Search_String='are';

SELECT 
CASE 
	WHEN CHARINDEX(@Search_String,@String)>0 
	THEN ( LEN(@String) - LEN(REPLACE(@String,@Search_String,'')) ) / LEN(@Search_String) 
	ELSE 0
	END AS Particular_Word_Count;

IMAGE

Conclusion

Thus we saw how to find the number of occurrences of a Particular Word inside the String easily using T-SQL. The above works well in both Azure SQL db and On-Premises.

Thanks for reading!

FOLLOW BLOG for interesting upcoming posts!

Advertisement

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 )

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