Execute the following SQL Server T-SQL script in Query Editor to create and apply a user-defined function (UDF) to count the occurrences of a string within another string:

USE AdventureWorks;

GO

-- SQL user-defined function - SQL scalar function - SQL count string occurrences

CREATE FUNCTION fnCountOccurences

               (@ShortString VARCHAR(100),

                @LongString  VARCHAR(8000))

RETURNS INT

AS

  BEGIN

    DECLARE  @Text      VARCHAR(8000),

             @Frequency INT

    SET @Text = @LongString

    SET @Text = REPLACE(@Text,@ShortString,'')

    SET @Frequency = (len(rtrim(@LongString)) - len(rtrim(@Text)))

                    / len(rtrim(@ShortString))

    RETURN (@Frequency)

  END

GO

SELECT dbo.fnCountOccurences('fox','The fox ran in the forest after other foxes.')

GO

-- 2

SELECT dbo.fnCountOccurences('o','The fox ran in the forest after other foxes.')

GO

-- 4

Related article:

Count String Occurrence Function