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