July 14, 2012

String search

This function searches for a string and returns the number of occurrences it finds.

CREATE FUNCTION StringCount
(@SearchString VARCHAR(4000),
@SearchFor VARCHAR(200))
RETURNS INT
AS
BEGIN
DECLARE
@Position INT,
@Counter  INT

SET @Position = 0
SET @Counter  = 0

WHILE (CHARINDEX(@SearchFor,@SearchString) > 0)
 
BEGIN
SELECT @Counter = (@Counter + 1),
@Position = (CHARINDEX(@SearchFor,@SearchString) + 1)
SELECT @SearchString =
SUBSTRING(@SearchString,@Position,LEN(@SearchString))
END

RETURN @Counter
END
GO