Counting the number of occurrences of one string inside another in SQL

-- Setup: Create a blank function if none exists. This allows us to 
-- rerun this single script each time we modify this function

IF NOT EXISTS (SELECT * FROM sys.objects
 WHERE object_id = OBJECT_ID(N'dbo.com_CountString')
 AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
EXEC dbo.sp_executesql @statement = N'create function dbo.com_CountString() RETURNS INT AS BEGIN RETURN '''' END'
go

-- Create the actual function

/*====================================================================================
Counts the number of times @SearchString appears in @Input.
====================================================================================*/
ALTER FUNCTION dbo.com_CountString(@Input nVarChar(max), @SearchString nVarChar(1000))
RETURNS INT
BEGIN
    DECLARE @Count INT, @Index INT, @InputLength INT, @SearchLength INT
    DECLARE @SampleString INT

    if @Input is null or @SearchString is null
        return 0

    SET @Count = 0
    SET @Index = 1
    SET @InputLength  = LEN(@Input)
    SET @SearchLength = LEN(@SearchString)

    if @InputLength = 0 or @SearchLength = 0 or @SearchLength > @InputLength
        return 0

    WHILE @Index <= @InputLength - @SearchLength + 1
    BEGIN
        IF SUBSTRING(@Input, @Index, @SearchLength) = @SearchString
        BEGIN
            SET @Count = @Count + 1
            SET @Index = @Index + @SearchLength
        END
        ELSE
            SET @Index = @Index + 1
    END

    RETURN @Count
END
GO

SELECT dbo.com_CountString('This is a string', 'is')

SELECT dbo.com_CountString(MyTable.MyColumn, 'search string')
FROM  MyTable
WHERE MyTable.MyKey = @Key