Execute the following script in Query Editor to create and apply a user-defined function to count the occurances of a string within another string:

USE AdventureWorks;
GO

CREATE FUNCTION fnCountOccurances(
@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.fnCountOccurances ('fox', 'The fox ran in the forest after other foxes.')
GO

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