Count # occurrences of one string inside another

IF EXISTS (SELECT 1
           FROM   sysobjects
           WHERE  xtype = 'FN'
             AND  name  = 'f_contains')
  DROP FUNCTION f_contains
GO
CREATE FUNCTION dbo.f_contains (@f_search_for VARCHAR (8000)
                              , @f_container  VARCHAR (8000))
RETURNS INT
/*  
||======================================================================  
|| Date created:  7/17/2002 (Dise)
||
|| Purpose:  This function counts the number of times one string occurs
||           within another string.
|| 
|| Parameters:
||   @f_search_for -- the string for which to search
||   @f_container  -- the string in which to search
||
|| Returns:  INT
||======================================================================
*/AS
BEGIN
  /*  
  ||====================================================================  
  || Local variable declarations...
  ||====================================================================
  */  DECLARE @count INT
        , @pos   INT
        , @len   INT

  /*  
  ||====================================================================  
  || Initializations...
  ||====================================================================
  */  SELECT @count = 0
       , @len   = LEN (@f_container)
       , @pos   = CHARINDEX (@f_search_for, @f_container)

  /*  
  ||====================================================================
  || If no occurrences of the searched-for string were found, the loop
  || below will fail to execute even once, and the function will thus
  || return zero to the calling statement.
  ||
  || Assuming at least one occurrence is found, the logic will loop and
  || count until no more occurrences are found...
  ||====================================================================
  */  WHILE @pos > 0
    AND @pos <= @len
  BEGIN
    SELECT @count = @count + 1
         , @pos   = @pos   + 1

    IF @pos < @len
  --THEN
      SELECT @pos = CHARINDEX (@f_search_for, @f_container, @pos)
    ELSE
      SELECT @pos = 0
  --END IF
  END

  RETURN @count
END
GO