OccursAny

ALTER FUNCTION dbo.OccursAny
	(
	@SearchExpression VARCHAR(255), 
	@ExpressionSearch VARCHAR(4000)
	)
RETURNS INT
AS
	BEGIN	
		DECLARE  @Delimiter varchar(10),
				 @Delimiter2 varchar(12),
				 @item nvarchar(4000),
				 @iPos int,
				 @DelimWidth int,
				 @iRetVal INT
		
		SET @iRetVal = 0
		SET @Delimiter = ','
	
		SET @Delimiter2 = @Delimiter 
		SET @Delimiter2 = ISNULL(@Delimiter2, ',')
		SET @DelimWidth = LEN(@Delimiter2) 

		IF RIGHT(RTRIM(@SearchExpression), 1) <> @Delimiter2      
			SELECT @SearchExpression = RTRIM(@SearchExpression) + @Delimiter2
		
		IF LEFT(@Delimiter2, 1) <> '%' 
			SET @Delimiter2 = '%' + @Delimiter2
		
		IF RIGHT(@Delimiter2, 1) <> '%' 
			SET @Delimiter2 = @Delimiter2 + '%'
		
		SELECT @iPos = PATINDEX(@Delimiter2, @SearchExpression) 
		
		WHILE @iPos > 0
		BEGIN 
			SELECT @item = LTRIM(RTRIM(LEFT(@SearchExpression, @iPos - 1)))
			IF @@ERROR <> 0 BREAK
			SELECT @SearchExpression =  RIGHT(@SearchExpression, LEN(@SearchExpression) - (LEN(@item) + @DelimWidth))
			IF @@ERROR <> 0 BREAK
			
			--INSERT INTO @tblSplit VALUES(@item)
			SET @iRetVal = @iRetVal + dbo.Occurs(@item, @ExpressionSearch)
			IF @@ERROR <> 0 BREAK
		
			SELECT @iPos = PATINDEX(@Delimiter2, @SearchExpression) 
			IF @@ERROR <> 0 BREAK
		END
				
	RETURN @iRetVal  
	END