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

Using the pattern that Microsoft SQL Server Management Studio creates, it would be something along the lines of:

IF  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'))
DROP FUNCTION [dbo].[com_CountString]
GO
/*====================================================================================
  Counts the number of times @SearchString appears in @Input.
  ====================================================================================*/
CREATE FUNCTION [dbo].[com_CountString](@Input nVarChar(max), @SearchString nVarChar(1000))RETURNS INT
BEGIN
    DECLARE
      @Count INT,
      @Index INT,
      @InputLength INT,
      @SearchLength INT,
      @SampleString INT
 
    IF ((@Input IS NULL) OR (@SearchString IS NULL))
        RETURN 0
 
    SELECT
      @Count = 0,
      @Index = 1,
      @InputLength = LEN(@Input),
      @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
            SELECT
              @Count = @Count + 1,
              @Index = @Index + @SearchLength
        END
        ELSE
        BEGIN
            SET @Index = @Index + 1
        END
    END
 
    RETURN @Count
    END
GO

I went ahead and updated the set statements as well. But using the pattern for dropping would get you out of updating it when that signature changes at all. It keeps it a bit more flexible and less tied to the exact implementation. :cool: