T-SQL – How to get the count of occurrence of a keyword in a string

If there is a requirement to check how many times a particular keyword comes in a string then below T-SQL Code will be helpful in getting occurrence count.

USE [MyDB]

GO

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

CREATE FUNCTION [dbo].[CountOccurancesOfString]

(

    @searchString nvarchar(max),

    @searchTerm nvarchar(max)

)

RETURNS INT

AS

BEGIN

    return (LEN(@searchString)-LEN(REPLACE(@searchString,@searchTerm,'')))/LEN(@searchTerm)

END

GO

--Executing the function

SELECT [dbo].[CountOccurancesOfString]('Life is beautiful,Life is not about finding yourself. Life is about creating yourself. so live life.' ,'Life')

                        -----------------End of Article-------------