Count string Items

,

This script will return how many time one string occurs within another. It will only work on SQL 2K as it is a UDF, but it could be converted to a stored proc with a output variable for SQL 7.0.

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

/****** Object:  User Defined Function dbo.fnCountStringItems    Script Date: 11/15/2002 1:54:56 PM ******/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[fnCountStringItems]') and xtype in (N'FN', N'IF', N'TF'))
drop function [dbo].[fnCountStringItems]
GO


CREATE   FUNCTION fnCountStringItems (@StringToLookIn varchar(8000), 
	 @StringToLookFor varchar(1000))
RETURNS int
AS
BEGIN

	DECLARE @cnt int,
		@tempstr varchar(8000)
	
	SET @cnt = 0

	IF LEN(@StringToLookFor) > 0 	
	BEGIN
		SET @tempstr = REPLACE (@StringToLookIn, @StringToLookFor, '')
	
		SET @cnt = LEN(@StringToLookIn) - LEN(@tempstr)
		
		SET @cnt = @cnt / LEN(@StringToLookFor)
	END
	
	RETURN @cnt
END



GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

Rate

Share

Share

Rate