SQL Server: Count Number of Occurrences of a Character or Word in a String

In this blog you will learn how to count the number of occurrences of a character or word in a string in SQL Server .

DECLARE @string VARCHAR(MAX)='Noida, short for the New Okhla Industrial Development Authority, is a planned city in India under the management of the New Okhla Industrial Development Authority.It is part of National Capital Region of India.

DECLARE @tosearch VARCHAR(MAX)='In'

SELECT (DATALENGTH(@string)-DATALENGTH(REPLACE(@string,@tosearch,'')))/DATALENGTH(@tosearch)
AS OccurrenceCount